Values in SPSS (like 'don't know' & 'not applicable) are imported into SAS as missings

[AWillems]

I tried to import a SPSS file into SAS, using this command:

proc import out = /new datafile in SAS/
datafile = "Location of original SPSS file";
dbms = SAV replace;
fmtlib = work.formats;

run;

Everything works out fine, except the predefined values in SPSS 66 'not applicable' and 88 'don't know' are changed into " . " missings.

Any suggestions?

Thanks

[Tom]

Did it create formats in the WORK.FORMATS catalog for any of those variables?

Can you provide a small example SAV file with just a couple of variables and a few observations that demonstrate the issue?

[AWillems]

SAS creates a FORMATS catalog in my own created library (other than work).

I've created a subset of the sample. Variable 'hulpnood' refers to be in need of help. If a persons answers with 'no' all the other variables are not applicable (value 66) and not missing ( .  )

/* 1) Import SPSS datfile*/

proc import out = test_spss
datafile = "test_spss.sav"
dbms = sav
replace;
fmtlib = work.formats;
run;

proc datasets lib = work nolist nodetails;
contents data = test_spss;
quit;

proc print data= test_spss;
run;

[Tom]

When I import that data NONE of the variables have ANY missing values.  Some of them do have the value 66.

Are you saying that PROC IMPORT has converted the 99's to 66's?

----------------------------------------------------------------------------
|       FORMAT NAME: PROF_HA  LENGTH:   14   NUMBER OF VALUES:    4        |
|   MIN LENGTH:   1  MAX LENGTH:  14  DEFAULT LENGTH:  14  FUZZ:        0  |
|--------------------------------------------------------------------------|
|START           |END             |LABEL  (VER. V7|V8   03SEP2021:08:48:41)|
|----------------+----------------+----------------------------------------|
|               0|               0|No                                      |
|               1|               1|Yes                                     |
|              66|              66|Not applicable                          |
|              99|              99|No answer                               |
----------------------------------------------------------------------------

                                           Cumulative    Cumulative
        prof_h    Frequency     Percent     Frequency      Percent
-------------------------------------------------------------------
No                       1       33.33             1        33.33
Not applicable           2       66.67             3       100.00

                                   Cumulative    Cumulative
prof_h    Frequency     Percent     Frequency      Percent
-----------------------------------------------------------
     0           1       33.33             1        33.33
    66           2       66.67             3       100.00

[Quentin · Accepted solution]

I would suggest changing the SPSS data so that these values are not defined as "user missing values" in SPSS.  That should let SAS import them as numbers.  Then in SAS you can recode them to special missing values, .N .D etc.  My guess is PROC IMPORT doesn't have logic for converting SPSS user missing values into SAS special missing values, so it just converts all the SPSS user missing values to the default SAS missing value.   Since SPSS only allows 3 user-missing values per column, in theory PROC IMPORT could have been coded to convert the three values to .A .B and .C, but I'm guessing the PROC IMPORT developer didn't bother with that wrinkle.

Luckily, once you have the values in SAS, recoding them to special missing values with an array is straight forward.  And I would assume on the SPSS side it should be possible to loop through all your variables and remove the user-missing values.  But I haven't used SPSS in 27 years...

[AWillems]

Thanks for the advice and it actually worked!

I've changed in the SPSS data set (variable view) the missing to none instead of 66, 88,... Now SAS reads all the values and doesn't convert all the SPSS missing values to the default SAS missing values (because there are no missing values any more)