CELLRANGE

[Palau, Joe J]

Hi,
 
Using the GET DATA command is it possible for SPSS to pull data from a
specified cell range even some of the fields in that range are blank?
For example, I specify a CELLRANGE of J1:J300 and some subjects have
data for all 300 cells, while others only the first 50. Can GET DATA
return -9s for the remaining missing values?
 
Thanks!
 
------------------------------------------
Joe J. Palau
Health Services Research & Development Unit
3350 La Jolla Vlg Dr (111N-1)
San Diego, CA 92161
 
phone: [phone] x 3054
fax:  [phone]
------------------------------------------
 

[Melissa Ives]

Joe,

It looks like you are talking about reading Excel data into SPSS.
If so...
Do you really mean to request only one column of data (J1:J330 or do you
mean A1:J300)?
Are your data arranged by subject in columns (as it appears) or in rows?

I don't know of a way to convert data while reading it in,
but you could change it in Excel before you read it in:
i.e. Highlight the cell range you want to replace blanks with -9, under
Edit, click Replace (or use Ctrl-H),
leave the find blank and in the Replace field, type -9, then click
'Replace All'.  It worked for me when I just tried it using Excel 2000.

OR in SPSS after:

i.e.
If (sysmis(varname)) varname=-9.
Missing values varname (-9).

If you need it, others can give the syntax for doing this over 300
variables with do repeat or vectors.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Palau, Joe J
Sent: Wednesday, January 10, 2007 12:37 PM
To: [hidden email]
Subject: [SPSSX-L] CELLRANGE

Hi,

Using the GET DATA command is it possible for SPSS to pull data from a
specified cell range even some of the fields in that range are blank?
For example, I specify a CELLRANGE of J1:J300 and some subjects have
data for all 300 cells, while others only the first 50. Can GET DATA
return -9s for the remaining missing values?

Thanks!

------------------------------------------
Joe J. Palau
Health Services Research & Development Unit 3350 La Jolla Vlg Dr
(111N-1) San Diego, CA 92161

phone: [phone] x 3054
fax:  [phone]
------------------------------------------




PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Oliver, Richard]

For numeric variables, blank cells will be set to system-missing. For string variables, blank cells will be blank string values. So...

recode numvar (missing=-9) (else=copy)
  /stringvar (" "="missing") (else=copy).
missing values numvar (-9) stringvar ("missing").






-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Palau, Joe J
Sent: Wednesday, January 10, 2007 12:37 PM
To: [hidden email]
Subject: CELLRANGE

Hi,

Using the GET DATA command is it possible for SPSS to pull data from a
specified cell range even some of the fields in that range are blank?
For example, I specify a CELLRANGE of J1:J300 and some subjects have
data for all 300 cells, while others only the first 50. Can GET DATA
return -9s for the remaining missing values?

Thanks!

------------------------------------------
Joe J. Palau
Health Services Research & Development Unit
3350 La Jolla Vlg Dr (111N-1)
San Diego, CA 92161

phone: [phone] x 3054
fax:  [phone]
------------------------------------------

[Melissa Ives]

Thanks Richard,

Note however that long string variable cannot have missing values set.
Strings >8 characters are considered long strings.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Oliver, Richard
Sent: Wednesday, January 10, 2007 1:40 PM
To: [hidden email]
Subject: Re: [SPSSX-L] CELLRANGE

For numeric variables, blank cells will be set to system-missing. For
string variables, blank cells will be blank string values. So...

recode numvar (missing=-9) (else=copy)
  /stringvar (" "="missing") (else=copy).
missing values numvar (-9) stringvar ("missing").






-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Palau, Joe J
Sent: Wednesday, January 10, 2007 12:37 PM
To: [hidden email]
Subject: CELLRANGE

Hi,

Using the GET DATA command is it possible for SPSS to pull data from a
specified cell range even some of the fields in that range are blank?
For example, I specify a CELLRANGE of J1:J300 and some subjects have
data for all 300 cells, while others only the first 50. Can GET DATA
return -9s for the remaining missing values?

Thanks!

------------------------------------------
Joe J. Palau
Health Services Research & Development Unit 3350 La Jolla Vlg Dr
(111N-1) San Diego, CA 92161

phone: [phone] x 3054
fax:  [phone]
------------------------------------------



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Oliver, Richard]

Good point. A limitation we hope to eliminate in the near future.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Melissa Ives
Sent: Wednesday, January 10, 2007 1:48 PM
To: [hidden email]
Subject: Re: CELLRANGE

Thanks Richard,

Note however that long string variable cannot have missing values set.
Strings >8 characters are considered long strings.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Oliver, Richard
Sent: Wednesday, January 10, 2007 1:40 PM
To: [hidden email]
Subject: Re: [SPSSX-L] CELLRANGE

For numeric variables, blank cells will be set to system-missing. For
string variables, blank cells will be blank string values. So...

recode numvar (missing=-9) (else=copy)
  /stringvar (" "="missing") (else=copy).
missing values numvar (-9) stringvar ("missing").






-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Palau, Joe J
Sent: Wednesday, January 10, 2007 12:37 PM
To: [hidden email]
Subject: CELLRANGE

Hi,

Using the GET DATA command is it possible for SPSS to pull data from a
specified cell range even some of the fields in that range are blank?
For example, I specify a CELLRANGE of J1:J300 and some subjects have
data for all 300 cells, while others only the first 50. Can GET DATA
return -9s for the remaining missing values?

Thanks!

------------------------------------------
Joe J. Palau
Health Services Research & Development Unit 3350 La Jolla Vlg Dr
(111N-1) San Diego, CA 92161

phone: [phone] x 3054
fax:  [phone]
------------------------------------------



PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Richard Ristow]

Melissa Ives pointed out,

>Note however that long string variable cannot have missing values set.
>Strings >8 characters are considered long strings.

At 03:47 PM 1/10/2007, Oliver, Richard wrote:

>Good point. A limitation we hope to eliminate in the near future.

Oh, please, please, please! That'll be SUCH a help.

For one use, for selecting values from different records in a group,
using AGGREGATE. Like this(1):

.  ADD FILES FILE=A
            /FILE=B
            /BY=state year.
.  AGGREGATE OUTFILE=*
           /PRE
           /BREAK=state year
           /age date height weight=FIRST(age date height weight).

This code assumes that variables are missing in cases from a file in
which they don't appear. That is true for numeric variables, but not
for strings.

One could use the following variation, adding the MISSING VALUES
statement:

.  ADD FILES FILE=A
            /FILE=B
            /BY=state year.
.  MISSING VALUES (<list of all string variables>) ' '.
.  AGGREGATE OUTFILE=*
            /PRE
            /BREAK=state year
            /age date height weight
      =FIRST(age date height weight).

But that won't work for the long string variables. Which allowing
missing values for long string variables would finally fix, oh joy, oh
joy.

-With thanks and anticipation,
  Richard

.......................
(1) Code: see posting
Message-ID:  <001801c72c37$7dca6cb0$6600a8c0@default6393bf1>
Date:    Sat, 30 Dec 2006 12:25:24 -0500
From:    Raynald Levesque <[hidden email]>
Subject: Re: Merging Dissimilar Files
To: [hidden email]

[Art Kendall]

It would be great to have missing values for long strings.

It also would often be useful to expand the number of ranges for missing
values.


Art Kendall
Social Research Consultants

Richard Ristow wrote:

> Melissa Ives pointed out,
>
>> Note however that long string variable cannot have missing values set.
>> Strings >8 characters are considered long strings.
>
>
> At 03:47 PM 1/10/2007, Oliver, Richard wrote:
>
>> Good point. A limitation we hope to eliminate in the near future.
>
>
> Oh, please, please, please! That'll be SUCH a help.
>
> For one use, for selecting values from different records in a group,
> using AGGREGATE. Like this(1):
>
> .  ADD FILES FILE=A
>            /FILE=B
>            /BY=state year.
> .  AGGREGATE OUTFILE=*
>           /PRE
>           /BREAK=state year
>           /age date height weight=FIRST(age date height weight).
>
> This code assumes that variables are missing in cases from a file in
> which they don't appear. That is true for numeric variables, but not
> for strings.
>
> One could use the following variation, adding the MISSING VALUES
> statement:
>
> .  ADD FILES FILE=A
>            /FILE=B
>            /BY=state year.
> .  MISSING VALUES (<list of all string variables>) ' '.
> .  AGGREGATE OUTFILE=*
>            /PRE
>            /BREAK=state year
>            /age date height weight
>      =FIRST(age date height weight).
>
> But that won't work for the long string variables. Which allowing
> missing values for long string variables would finally fix, oh joy, oh
> joy.
>
> -With thanks and anticipation,
>  Richard
>
> .......................
> (1) Code: see posting
> Message-ID:  <001801c72c37$7dca6cb0$6600a8c0@default6393bf1>
> Date:    Sat, 30 Dec 2006 12:25:24 -0500
> From:    Raynald Levesque <[hidden email]>
> Subject: Re: Merging Dissimilar Files
> To: [hidden email]
>
>

[Melissa Ives]

YES!!! That WOULD be very useful!  To have more than 3 distinct values
assigned as missing.

Melissa

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Art Kendall
Sent: Thursday, January 11, 2007 7:16 AM
To: [hidden email]
Subject: Re: [SPSSX-L] Missing values for long strings (was, re:
CELLRANGE)

It also would often be useful to expand the number of ranges for missing
values.


Art Kendall
<snip>


PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.