How to get value labels from SAS to Stata

[bourne3rd]

I have been struggling to get data transferred from SAS to Stata for the past few days. My trouble is that while I can get the variable labels transferred between them, I cannot get the value labels transferred.

For example, if a variable is year_in_college, the variable label is `Year in College', and the value labels are `Freshman' for the value 1, `Sophomore' for the value 2, etc., then I can get the variable label into Stata, but the value labels of `Freshman' etc, do not appear in Stata.

I have tried to find a solution without success. The relevant SAS code that I have been trying without success is the following:

PROC EXPORT DATA= OUT.data_to_export

    OUTFILE = `C:\Users\NAME\Desktop\Data\stata_dataset.dta''

    DBMS= STATA LABEL REPLACE;

    FMTLIB= LIB.FORMATS;

RUN;

[heffo]

Proc Export sends the data to the file, not the labels. So, if you want to send the formatted values, you will have to create a new variable with a put statement. Then you can export that file. 

data want;
	set have;
	length year_in_college_t $ 32; 
		*Make sure that the length of the string variable is long enough for the longest label in the format.;
	year_in_college_t=put(year_in_college,MyCollFormat.); *Use your format here.;
	*Add other variables and formats here.;
	drop year_in_college;
run;

*Alternatively if you need to keep the variable names ;
data want;
	set have (rename=year_in_college=year_in_college_old;
	length year_in_college $ 32; 
		*Make sure that the length of the string variable is long enough for the longest label in the format.;
	year_in_college=put(year_in_college_old,MyCollFormat.); *Use your format here.;
	*Add other variables and formats here.;
	drop year_in_college_old;
run;

[bourne3rd]

Thanks for the quick reply! Does there happen to be a faster way? Unfortunately, I am dealing with hundreds of variables so this would be quite tedious to do for all of them.

[heffo · Accepted solution]

I'm not sure it will work fully and it does change the order of the table. But here it is.

*Create a custom format;
proc format;
	value $sex 	"F" = "Female"
				"M" = "Male";
	value age 	1-10 = "Youngling"
				11-18 = "Master";
run;
*use the custom format;
data class;
	set sashelp.class;
	format sex $sex. age age. Weight 4.;
run;

*Actual macro to be used;
%macro reformat(ds_in, ds_out);
	*Get the variables with formats in them;
	proc contents data=&ds_in out=variables (keep=Name format where=(format is not missing)) noprint ;
	run;
	*Fix that the formats migth not have a . in them.;
	data variables;
		set variables;
		*Do not touch numeric formats that is on the form w.d or w.;
		if lengthn(compress(format,"1234567890.,"))>0;
		if index(format,".") = 0 then format = cats(format,".");
	run;

	*Create macro variables to be used in the data step.;
	proc sql noprint;
		select name into : variables separated by " " 
		from variables;		
		select cats(name,"_old") into : variables_old separated by " " 
		from variables;		
		select cats(name,"=",name,"_old") into : rename separated by " " 
		from variables;
		select cats(name,"=put(",name,"_old,",format,");") into : reformat separated by " " 
		from variables;
	quit;
	*Create the new data set. ;
	data &ds_out;
		length &variables $ 32;
		set &ds_in (rename=(&rename));
		&reformat;
		drop &variables_old;
	run;

%mend reformat;

*Call the format. Your data set and the output data set;
%reformat(work.class,work.class2);

[bourne3rd]

This worked great! Thanks so much--you've saved me a lot of time.