How to prepare a format file in SAS for SPSS users-How to export SAS dataset with formats to SPSS

[Emma_at_SAS]

I prepared a dataset and its format file (with value labels and variable labels) in SAS. The format file I created is a SAS program with .sas extension. However, it sounds like I need a .sas7bdat type of format file to access it in SPSS. How do you collaborate with SPSS users for sharing datasets?

Thanks! 

[Emma_at_SAS]

@Reeza in another post here https://communities.sas.com/t5/SAS-Programming/convert-SAS-file-to-SPSS/m-p/233538#M42648

you suggested PROC EXPORT. May you please provide more details for your suggestion? Does that mean I can export the SAS dataset with its format file as an SPSS data file?

Thanks 

[Reeza]

Honestly haven't worked with SPSS in about a decade now. In 2015 that was from memory...I would definitely try exporting directly to a SAV file and see how that works but I suspect you need to manually do the mapping/conversion on the file before you transfer it. I was once considering creating a macro to do that but I never got around to it....

[Emma_at_SAS]

Thank you very much, Reeza, for taking the time to respond to my post and for your thoughts. It was not in my mind to exporting directly to a .sav file and PROC EXPORT works very well and exports some value and variable labels (I do not know why not all). I am looking for advice to use the FMTLIB option to export all labels. Thanks!

[Reeza]

FMTLIB works in the opposite direction - for reading in the file IIRC not how to write the file.

For the formats that are not applied, are they user defined formats? Character formats? Numeric formats?

[Emma_at_SAS]

Thank you, for your thoughts. I received the original data in SPSS (.sav) and it contained value labels. I derived some variables in SAS and added value labels and variable labels for the derived variables in my SAS format file.
The SPSS dataset that I exported from SAS, contains all the variable labels, some value labels from that original SPSS dataset (not all of them) and some of the ones I created for the derived variables. In SPSS, all the missing value labels are recognized as numeric similar to my STUDENT example below:
1 = "yes"
2 = "no"

[Tom]

STEP 1.  When you import  the SPSS file into a SAS dataset using PROC IMPORT you should have specified the FMTLIB= option to tell PROC IMPORT what format catalog to write the formats it generated from the value labels.

STEP 2. You should make sure that the FMTSEARCH option includes that catalog so that when you use the data in SAS the formats are available to be used to display the values.

STEP 3.  When you generated new formats to attach to your new variables you should make sure to tell PROC FORMAT to write those new formats into the same catalog as you used in step 1 and step 2.

STEP 4. When you generate the new variables that need to use these new formats make sure to include the FORMAT statement in the data step that creates the variables.  (Of if you make them with PROC SQL code then include the FORMAT= keyword when generating the new variable).

STEP 5.  Now export to SPSS file using PROC EXPORT.  I think you will also need to specify the FMTLIB= option here to tell it where to find the format definitions so it knows how to create value labels.

But you still might have issues with some formats as I don't think PROC EXPORT is that smart.  Essentially it should be able to handle simple 1 to 1 mapping of numeric values to strings, like your posted example STUDENT format.  But if you are using ranges or OTHER groups then it probably will not be able to translate that.

[Emma_at_SAS]

Thank you, Tom, for these steps. May you please help me fix my STEP 5?

I cannot introduce the format file in the FMTLIB option. This is my try

---

libname thelib "D:\path"; 
PROC EXPORT DATA=SAS_data 
            FILE="&spss_loc\SPSS_data.sav"
            DBMS=SPSS REPLACE ;
            FMTLIB= thelib.format_file_name;			
RUN;

This code creates the SPSS dataset but now all value labels are removed.

The log looks fine

NOTE: The export data set has 450 observations and 85 variables.
NOTE: "D:path\SPSS_data.sav" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 5.20 seconds
cpu time 0.01 seconds

Thanks!

[Reeza]

I think it's Step 4 you're missing. Can you please show that step.

[Emma_at_SAS]

@Reeza Good point. May you please check if my step 4 is correct?

data sas_data_new (rename (this=that));
set sas_data_with_derived_var(drop=var1 var2);
run;
%format_file (d1 = sas_data_new, d2 = sas_data); 

After adding the derived variables, in the last step, I rename and drop some variables and then match my SAS data (sas_data_new) with the format file using the %format_file I created in my format file and the d2=sas_data has all the variable and value labels. This is the SAS data I export to SPSS. Am I missing something here? Thanks!

From previous posts:

This is my format file-->

proc format;
value student 
1 = "yes"
2 = "no";
.
.
.
%macro format_file (d1 = , d2 = );
data &d2;
set &d1;
format student student.;
.
.
.
label student = "are you currently a student?";
.
.
.
run;
%mend;

and, this is my code to EXPORT the SAS data as an SPSS dataset-->

libname thelib "D:\path"; 
PROC EXPORT DATA=sas_data 
            FILE="&spss_loc\SPSS_data.sav"
            DBMS=SPSS REPLACE ;
            FMTLIB= thelib.format_file_name;			
RUN;

"D:\path"; is where I saved my format file "format_file_name.sas"

[Tom]

Format catalogs do NOT have an extension of .SAS.  That is used for text files that have SAS code in them.

Start with an existing spss file.

filename myfile 'some filename';

Import it into a SAS dataset and a SAS format catalog.

libname mylib 'where I am writing my SAS files';
proc import
  file=myfile dbms=spss
  out=mylib.mydataset  replace
;
  fmtlib=mylib.formats;
run;

Tell SAS to search the new catalog to be able to find the formats.

options append=(fmtsearch=(mylib.formats));

Define some new formats:

proc format lib=mylib.formats;
  value student 1='Yes' 2='No' ;
run;

Create some new data:

data mylib.mynewdata;
  set mylib.mydataset;
  student=rand('integer',1,2);
  format student student.;
run;

Export a new SAV file.

filename out 'new SPSS filename';
proc export dbms=spss data=mylib.mynewdata
   file=out replace
;
   fmtlib=mylib.formats;
run;

Now open the new SAV file in SPSS and see what happened.

[Reeza]

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/p06q1qcxvfcjlhn1u2ziy60inadl.htm

Write SAS format values defined in the format catalog to the JMP file for the value labels.
Interaction: The FMTLIB statement is valid only when DBMS=JMP.

I don't think the FMTLIB is the correct approach.

You mentioned that you created the formats in your format file. I'm assuming you applied the formats as well before trying to export the data?

[Tom]

SPSS does not have the concept of a FORMAT the way that SAS does.  Instead is stores the code/decode logic for each variable separately in the data file.

You should not have to "prepare" anything.  Just make sure the formats are attached to the variables and that PROC EXPORT knows where to find the formats by using the FMTLIB= option.

https://documentation.sas.com/doc/en/vdmmlcdc/8.1/acpcref/n1h6b01uh0dm2yn1fpq85gjgwcfs.htm  

If you did want to send the format definitions as data to SPSS then first convert them into a SAS dataset by using the CNTLOUT= option on PROC FORMAT.  Then export that dataset to SPSS data using PROC EXPORT.  You will not be able to use them as value labels that way, but you will be able to access them as data.

[Emma_at_SAS]

Thank you very much, Tom! 

May you please help me to add the FMTLIB option appropriately? my format file is a .SAS file which only contained the value and variable labels with a proc format at the beginning like this:

proc format;
value student 
1 = "yes"
2 = "no";
.
.
.
%macro format_file (d1 = , d2 = );
data &d2;
set &d1;
format student student.;
.
.
.
label student = "are you currently a student?";
.
.
.
run;
%mend;

I could use the following code to export my SAS data set and save it as a SPSS_data.sav file.

PROC EXPORT DATA=SAS_data 
            FILE="&spss_loc\SPSS_data.sav"
            DBMS=SPSS REPLACE ;			
RUN;

Without the FMTLIB option, the variable labels and some of the value labels are also in the SPSS dataset but I do not know where/how to add the FMTLIB option to export all variable and value labels.

Thanks!