Obtaining one race from those who check multiple races

[Jenny Steffes]

We are trying to look at patient demographic data from a survey we sent out.  In the survey, they were told that they could check all races that apply to them and asked them to indicate whether they are Hispanic/Latino or not.  However, now we want to break out this information so that we can report it as the government requests.  We want to identify anyone who checked African American (even if they checked other races) to only pull into the AA frequency (and not that other race they checked).

Currently it looks something like this:
Patient IDWhiteAAAsianNative HawaiianAmer.In
00111000
00201101
00300110


In the above scenario, we would want patient 001 and 002 to only be counted as African American.  We tried with Access and it is just too difficult.  Is there an easy way to do this with SPSS?
Thanks,
Jenny Steffes
Research Associate

[Richard Ristow]

At 03:45 PM 12/7/2006, Jenny Steffes wrote:

>We are trying to look at patient demographic data from a survey we
>sent out.  In the survey, they were told that they could check all
>races that apply to them and asked them to indicate whether they are
>Hispanic/Latino or not.  However, now we want to break out this
>information so that we can report it as the government requests.  We
>want to identify anyone who checked African American (even if they
>checked other races) to only pull into the AA frequency (and not that
>other race they checked).

Well, how to do it depends on the logic you want. One possibility, that
it sounds like you may want, is to list races in a priority order, and
assign respondents the highest-priority race they identify with. If,
say, the priority order is

AA > Hawaiian > Amer.In > Asian > White

something like this (NOT TESTED) should do it:

NUMERIC   One_Race (F2).
VAR LABEL One_Race 'Highest priority self-identified race'.
VAL LABEL One_Race
     1 'African-Amer'
     2 'Nat.Hawaiian'
     3 'Am. Indian'    AA > Hawaiian > Amer.In > Asian > White
     4 'Asian'
     5 'White'
     9 'NONE SELECTED'.

DO IF   NOT MISSING(AA)
                 AND AA       EQ 1.
.  COMPUTE ONE_RACE = 1.
ELSE IF NOT MISSING(Hawaiian)
                 AND Hawaiian EQ 1.
.  COMPUTE ONE_RACE = 2.
ELSE IF NOT MISSING(Amer.In)
                 AND Amer.In  EQ 1.
.  COMPUTE ONE_RACE = 3.
ELSE IF NOT MISSING(Asian)
                 AND Asian    EQ 1.
.  COMPUTE ONE_RACE = 4.
ELSE IF NOT MISSING(White)
                 AND White    EQ 1.
.  COMPUTE ONE_RACE = 5.
ELSE.
.  COMPUTE ONE_RACE = 9.
END IF.

The 'cute' bit in this code is including 'NOT MISSING' in the tests. If
a racial variable is missing, 'NOT MISSING' for that variable tests
False, so the whole test tests False. Otherwise, if, say, AA were
missing, the first test would test Missing, and the rest of the DO IF
construct would be skipped >WITH NO MESSAGE OR OTHER INDICATION<.

(Yes. Distinguishing True, False, and Missing as possible results of a
test, is something SPSS got right. Handling 'Missing' by skipping the
rest of a DO IF construct, with no warning and no automatic means of
recovery, they got pretty badly wrong.)

  tests. T

[Judith Saebel]

Hi all,

I've just had a strange problem with SPSS 14.0.  Even though the licence
for our university was meant to last until the end of August 2007, I'm
suddenly getting the following message:


>Error # 7002
>There appears to be a license for SPSS for Windows, but it is invalid.
>This command not executed.

>Specific symptom number: 26


I'm not asking for an advice how to fix this; I hope our technicians
will be able to do it next week. I'm merely curious to know if someone
else has encountered this problem and what it all means.

Thanks in advance for your ideas.

Sincerely,

Judith Saebel

[Beadle, ViAnn]

This is a classic multiple response situation. You can tabulate counts for each race, plus if you're only interested in African American you can select those records by using
Compute AA=any(AA,race1 to race6).
Filter by AA.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Richard Ristow
Sent: Friday, December 08, 2006 2:03 AM
To: [hidden email]
Subject: Re: Obtaining one race from those who check multiple races

At 03:45 PM 12/7/2006, Jenny Steffes wrote:

>We are trying to look at patient demographic data from a survey we
>sent out.  In the survey, they were told that they could check all
>races that apply to them and asked them to indicate whether they are
>Hispanic/Latino or not.  However, now we want to break out this
>information so that we can report it as the government requests.  We
>want to identify anyone who checked African American (even if they
>checked other races) to only pull into the AA frequency (and not that
>other race they checked).

Well, how to do it depends on the logic you want. One possibility, that
it sounds like you may want, is to list races in a priority order, and
assign respondents the highest-priority race they identify with. If,
say, the priority order is

AA > Hawaiian > Amer.In > Asian > White

something like this (NOT TESTED) should do it:

NUMERIC   One_Race (F2).
VAR LABEL One_Race 'Highest priority self-identified race'.
VAL LABEL One_Race
     1 'African-Amer'
     2 'Nat.Hawaiian'
     3 'Am. Indian'    AA > Hawaiian > Amer.In > Asian > White
     4 'Asian'
     5 'White'
     9 'NONE SELECTED'.

DO IF   NOT MISSING(AA)
                 AND AA       EQ 1.
.  COMPUTE ONE_RACE = 1.
ELSE IF NOT MISSING(Hawaiian)
                 AND Hawaiian EQ 1.
.  COMPUTE ONE_RACE = 2.
ELSE IF NOT MISSING(Amer.In)
                 AND Amer.In  EQ 1.
.  COMPUTE ONE_RACE = 3.
ELSE IF NOT MISSING(Asian)
                 AND Asian    EQ 1.
.  COMPUTE ONE_RACE = 4.
ELSE IF NOT MISSING(White)
                 AND White    EQ 1.
.  COMPUTE ONE_RACE = 5.
ELSE.
.  COMPUTE ONE_RACE = 9.
END IF.

The 'cute' bit in this code is including 'NOT MISSING' in the tests. If
a racial variable is missing, 'NOT MISSING' for that variable tests
False, so the whole test tests False. Otherwise, if, say, AA were
missing, the first test would test Missing, and the rest of the DO IF
construct would be skipped >WITH NO MESSAGE OR OTHER INDICATION<.

(Yes. Distinguishing True, False, and Missing as possible results of a
test, is something SPSS got right. Handling 'Missing' by skipping the
rest of a DO IF construct, with no warning and no automatic means of
recovery, they got pretty badly wrong.)

  tests. T

[Shauna Marie Wilhelm]

I am trying to save a very large data set into an excel file, but when I
open up the excel file all of the system missing cells have "Null!" Does
anyone have a suggestion as to how I can export the data into excel
without having the cells in excel show Null! I have tried to copy and
paste the data, but the data file is too large to copy all at once and I
am afraid I might forget some cases if I only copy a small section of
data at a time.

Thank you for your assistance in advance.

[Marks, Jim]

Recode your system missing values to a known number:

RECODE v1 v2 v3 (SYSMIS = -999).
MISSING VALUES v1 v2 v3 (-999).

The second command will tell spss to ignore the -999 values in
proceedures.

The number -999 should appear in your data when exported. Excel will
think it is a valid result, so be careful with formulas and calculations
in excel.

HTH

--jim



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Shauna Marie Wilhelm
Sent: Friday, December 08, 2006 9:40 AM
To: [hidden email]
Subject: Saving data from SPSS to Excel file.

I am trying to save a very large data set into an excel file, but when I
open up the excel file all of the system missing cells have "Null!" Does
anyone have a suggestion as to how I can export the data into excel
without having the cells in excel show Null! I have tried to copy and
paste the data, but the data file is too large to copy all at once and I
am afraid I might forget some cases if I only copy a small section of
data at a time.

Thank you for your assistance in advance.

[Egon Kraan]

Shauna,

Other than ensuring that your data is numeric and missing values are also
stated as values.  Make sure that your large data file fits in excel.

SPSS can handle far more variables and cases than Excel.  You are likely to
run into trouble if your data file is larger than what excel can handle.

For instance, SPSS can handle 2.5 billion cases and 2.5 billion variables.

Excel can handle a little under 66k rows and 256 columns.

If your data set is larger than that, you may want to look into exporting to
another application.. perhaps MS Access, or I believe Quattro Pro has larger
set limits.

Hope that helps

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]]On Behalf Of
Shauna Marie Wilhelm
Sent: Friday, December 08, 2006 10:40 AM
To: [hidden email]
Subject: Saving data from SPSS to Excel file.


I am trying to save a very large data set into an excel file, but when I
open up the excel file all of the system missing cells have "Null!" Does
anyone have a suggestion as to how I can export the data into excel
without having the cells in excel show Null! I have tried to copy and
paste the data, but the data file is too large to copy all at once and I
am afraid I might forget some cases if I only copy a small section of
data at a time.

Thank you for your assistance in advance.

[Mahbub Khandoker]

You can try to fix this issue in Excel.
Open your file in Excel then follow the following procedure,

Edit>Go to then Click Special to open "Go to Special" dialog box.
Then select constant radio button and uncheck numbers, text & logicals below Formulas radio button (keep check Errors). Click OK.
That will select all the "#NULL".
Now by hitting delete key, you can delete all "#NULL" in the sheet in one time.

Hopefully this will help you.
Cheers,
Mahbub


 -----Original Message-----
From:   SPSSX(r) Discussion [mailto:[hidden email]]  On Behalf Of Shauna Marie Wilhelm
Sent:   8-Dec-06 10:40 AM
To:     [hidden email]
Subject:             Saving data from SPSS to Excel file.

I am trying to save a very large data set into an excel file, but when I
open up the excel file all of the system missing cells have "Null!" Does
anyone have a suggestion as to how I can export the data into excel
without having the cells in excel show Null! I have tried to copy and
paste the data, but the data file is too large to copy all at once and I
am afraid I might forget some cases if I only copy a small section of
data at a time.

Thank you for your assistance in advance.

[Victor Kogler]

And there is also the  Edit > Replace route.  The #NULL! cell entry is a
text value that can be replaced by anything else - for example zero, a
space or, by leaving the Replace With field blank, an empty cell.

Victor Kogler

Mahbub Khandoker wrote:

> You can try to fix this issue in Excel.
> Open your file in Excel then follow the following procedure,
>
> Edit>Go to then Click Special to open "Go to Special" dialog box.
> Then select constant radio button and uncheck numbers, text & logicals below Formulas radio button (keep check Errors). Click OK.
> That will select all the "#NULL".
> Now by hitting delete key, you can delete all "#NULL" in the sheet in one time.
>
> Hopefully this will help you.
> Cheers,
> Mahbub
>
>
>  -----Original Message-----
> From:   SPSSX(r) Discussion [mailto:[hidden email]]  On Behalf Of Shauna Marie Wilhelm
> Sent:   8-Dec-06 10:40 AM
> To:     [hidden email]
> Subject:             Saving data from SPSS to Excel file.
>
> I am trying to save a very large data set into an excel file, but when I
> open up the excel file all of the system missing cells have "Null!" Does
> anyone have a suggestion as to how I can export the data into excel
> without having the cells in excel show Null! I have tried to copy and
> paste the data, but the data file is too large to copy all at once and I
> am afraid I might forget some cases if I only copy a small section of
> data at a time.
>
> Thank you for your assistance in advance.
>
>
>
>

[Oliver, Richard]

SPSS does, indeed, convert numeric system-missing values to #NULL! in Excel. But since this is simply a string in Excel, you can simply globally replace all instances of #NULL! with the standard Excel search/replace mechanism. Alternatively, you could save the file as tab-delimited text or as a CSV file (new in SPSS 15), and then open the text data file in Excel.

data list list (",") /var1 var2.
begin data
1,,
,2
3,,
,4
end data.

*sysmis will be #NULL! in Excel.
save translate /outfile='c:\temp\temp.xls'
  /type=xls /version=8 /fieldnames /replace.

*sysmis will be blank cells in Excel.
save translate /outfile='c:\temp\temp.txt'
  /type=tab /fieldnames /replace.

*new in SPSS 15.
save translate /outfile='c:\temp\temp.csv'
  /type=csv /fieldnames /replace.



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Victor Kogler
Sent: Friday, December 08, 2006 10:28 AM
To: [hidden email]
Subject: Re: Saving data from SPSS to Excel file.

And there is also the  Edit > Replace route.  The #NULL! cell entry is a text value that can be replaced by anything else - for example zero, a space or, by leaving the Replace With field blank, an empty cell.

Victor Kogler

Mahbub Khandoker wrote:

> You can try to fix this issue in Excel.
> Open your file in Excel then follow the following procedure,
>
> Edit>Go to then Click Special to open "Go to Special" dialog box.
> Then select constant radio button and uncheck numbers, text & logicals below Formulas radio button (keep check Errors). Click OK.
> That will select all the "#NULL".
> Now by hitting delete key, you can delete all "#NULL" in the sheet in one time.
>
> Hopefully this will help you.
> Cheers,
> Mahbub
>
>
>  -----Original Message-----
> From:   SPSSX(r) Discussion [mailto:[hidden email]]  On Behalf Of Shauna Marie Wilhelm
> Sent:   8-Dec-06 10:40 AM
> To:     [hidden email]
> Subject:             Saving data from SPSS to Excel file.
>
> I am trying to save a very large data set into an excel file, but when
> I open up the excel file all of the system missing cells have "Null!"
> Does anyone have a suggestion as to how I can export the data into
> excel without having the cells in excel show Null! I have tried to
> copy and paste the data, but the data file is too large to copy all at
> once and I am afraid I might forget some cases if I only copy a small
> section of data at a time.
>
> Thank you for your assistance in advance.
>
>
>
>