mixup with value labels after import from SPSS

[bouz22]

Hello all,

I use Sas mainly when I'm asked to provide files in .sav format because it can handle value labels with import and export of .sav files. However today I noticed something worrisome, after importing and exporting a file using below code the value labels had changed

proc import
	file = "file.sav"
	out = all_dr
	dbms = spss
	replace;
run;

proc export
	data = all_dr
	outfile = "file_out.sav"
	dbms=spss
	replace;
run;

There is an item called "marital status" which looks like this in the original file:

and after exporting has different value labels:

These new value labels come from another item about household status.

Is there something I could do to fix this or is it better to not use Sas for this purpose? In that case I would have to start using SPSS syntax for merging and filtering which I'm not really looking forward too..

Thanks for your help!

[Tom]

Can you provide more details.  Your first step should create WORK.ALL_DR dataset.  Show the PROC CONTENTS output. (preferable as text instead of photographs.)  What is the actual name of the variable you are referring to as "marital status" ?  What is it type? Numeric or Character?  Did PROC IMPORT attach a format to it?  What is that formats name?

SAS stores format definitions independently from the variables.  And independently from the datasets.

In neither step did you tell the procs where to write or find formats.  Try being specific about where you want IMPORT to write the format definitions and where PROC EXPORT should look to find format definitions.

Did it actually create any formats from the value labels?  Can you run PROC FORMAT with the FMTLIB option to see what format it created.

Did you import any other SPSS files that might have overwritten the format with something else?  Did you run an PROC FORMAT statements of your own that might have overwritten the formats.

[bouz22]

Thank you for the quick reply!

I ran the file again after restarting Sas and I only ran above lines of code (proc import/proc export) so no other format statements. That seems to have solved the issue.. I tried running the rest of the code to see if the problem occurred after loading other spss files and then I noticed there is a second variable with exactly the same value labels so I suppose that is where the problem arises.

From the original file:

MF_MARSTAT

Num

8

MF_MARS.

Marital Status

sl_cur_ms

Num

8

SL_CUR1A.

Current marital status

The labels of these two items are identical so I guess that's where the problem lies, although it only occurs after joining tables and/or loading other SPSS files. Strangely there is nothing in the proc contents of the joined and filtered output file that suggests the labels are different:

52

sl_cur_ms

Num

8

SL_CUR1A.

Current marital status

This is the output of proc fomat, with both the household and the marital status items:

 „ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ†           
           ‚       FORMAT NAME: SL_CUR1A LENGTH:   22   NUMBER OF VALUES:    5        ‚           
           ‚   MIN LENGTH:   1  MAX LENGTH:  22  DEFAULT LENGTH:  22  FUZZ:        0  ‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚START           ‚END             ‚LABEL  (VER. V7|V8   13MAR2020:15:01:49)‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚               1‚               1‚Single                                  ‚           
           ‚               2‚               2‚With parent(s)                          ‚           
           ‚               3‚               3‚With partner/family                     ‚           
           ‚               4‚               4‚Sheltered living (ggz)                  ‚           
           ‚               5‚               5‚Other                                   ‚           
           Šƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒŒ           
                                                                                                  
                                                                                                  
           „ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ†           
           ‚       FORMAT NAME: SL_CUR_  LENGTH:   22   NUMBER OF VALUES:    5        ‚           
           ‚   MIN LENGTH:   1  MAX LENGTH:  22  DEFAULT LENGTH:  22  FUZZ:        0  ‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚START           ‚END             ‚LABEL  (VER. V7|V8   13MAR2020:14:53:40)‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚               1‚               1‚Single                                  ‚           
           ‚               2‚               2‚With parent(s)                          ‚           
           ‚               3‚               3‚With partner/family                     ‚           
           ‚               4‚               4‚Sheltered living (ggz)                  ‚           
           „ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ†           
           ‚       FORMAT NAME: SL_CUR_  LENGTH:   22   NUMBER OF VALUES:    5        ‚           
           ‚   MIN LENGTH:   1  MAX LENGTH:  22  DEFAULT LENGTH:  22  FUZZ:        0  ‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ…ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚START           ‚END             ‚LABEL                           (CONT'D)‚           
           ‡ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒˆƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‰           
           ‚               5‚               5‚Other                                   ‚           
           Šƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ‹ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒŒ           

My apologies for the formatting mess, I don't find the option to get text back to outlined left after pasting the output.

How could I specify where to write the format definitions durint the IMPORT step?

[Tom · Accepted solution]

Use the FMTLIB option.  https://documentation.sas.com/?docsetId=proc&docsetTarget=p1b08qam0tmo97n1j27xceo6xsrl.htm&docsetVer...

You will probably want to use a separate format catalog for each SPSS file to avoid collision of format names.

Like I said, in SAS formats are independent.  So SL_CUR1A defines the fact that 1 maps to Single, etc.  You are then free to tell SAS which variables you want to use that decode for.  So if you have 20 questions that each have the same set of responses instead of making 20 different formats you would define just one and attach it to all 20 variables.  SPSS doesn't do that.  It stores the "value labels" with each variable's definition.  So if you want to have 20 variables use the same set of decodes that information is stored 20 times in the SPSS file.

Plus in SAS the decode information is NOT stored in the dataset.  Only the NAME of the format that is to be used to decode/display that variable is stored. The actual definition (1=Single) is stored in the format definition in a format catalog, a separate file.  So when you use the dataset you need to make sure to set the FMTSEARCH system option so that it can find the formats.  If the FMTSEARCH option is pointing to the wrong catalog it might not find the format, or it might find a different format that uses the same name.

[bouz22]

Thank you very much for the detailed explanation Tom, this was very helpful. I now created one library in the beginning of the Sas script and used that same library for all the .sav files I import. If I understand it correctly it will then reuse formats that are the same between two spss files and make new ones for new formats. Before exporting I used FMTSEARCH option and the labels are now coming out correct!

I couldn't figure out at first how to use the FMTLIB statement in proc import but I managed with this link:

https://documentation.sas.com/?docsetId=acpcref&docsetTarget=n1h6b01uh0dm2yn1fpq85gjgwcfs.htm&docset...

I'm foreseeing that I will have to keep using Sas to merge and filter SPSS files so I was quite desperate when I noticed this error, I'm happy it's solved now!