topic Re: Export SAS dataset to STATA keeping the formats in SAS Procedures

Export SAS dataset to STATA keeping the formats

Vincent5 — Tue, 03 Mar 2020 13:36:57 GMT

Hi, i have to convert my sas dataset into stata keeping the formats i created but i can't do it.

I've even tried to make it on an easy example but still don't work.

Here is my code:

libname formats "...\Formats";
option fmtsearch=(formats);

Proc format lib=formats;
value $nationalite
"1" = "Française"
"2" = "CEE"
"3" = "Hors CEE";
quit;

data test;
input a$;
cards;
1
1
2
2
3
;
run;

data test2;
set test;
format a $nationalite.;
run;

PROC EXPORT DATA=test2
OUTFILE="...\base.dta"			
DBMS=dta REPLACE;
fmtlib=formats;
RUN;

The formats are attached to my sas dataset, but when i export this to stata with fmtlib option, stata ignores my formats, do you know why? Something is missing?

Thank you!

Re: Export SAS dataset to STATA keeping the formats

ballardw — Tue, 03 Mar 2020 16:00:33 GMT

The note under exporting SAS to stat has this note related to value labels:

When writing SAS data to a Stata file, the EXPORT procedure saves the value labels that are associated with the variables. The procedure uses the formats that are associated with the variables to retrieve the value entries. You can use the FMTLIB=libref.format-catalog statement to tell SAS where to locate the formats catalog.

Note: Numeric formats only.

For character values you may need to go through a data step to create a new data set and create the formatted value(s) of character variables by using

newvar = put(variablename, characterformat.);

and then exporting that data set instead.

Re: Export SAS dataset to STATA keeping the formats

Vincent5 — Tue, 03 Mar 2020 16:36:11 GMT

Thank you for your answer, i didn't know that.

But i also tried converting character to numeric value before posting and that still don't apply format in STATA:

libname formats "C:\Users\vincent.dibeo\Documents\ANPAA - CSAPA\CSAPA sentinelle\Données\Formats\test";
option fmtsearch=(formats);

Proc format lib=formats;
value nationalite
1 = "Française"
2 = "CEE"
3 = "Hors CEE";
quit;

data test;
input a;
cards;
1
1
2
2
3
;
run;

data test2;
set test;
format a nationalite.;
run;


PROC EXPORT DATA=test2
OUTFILE="C:\Users\vincent.dibeo\Documents\ANPAA - CSAPA\CSAPA sentinelle\Données\Formats\test\base.dta"			
DBMS=dta REPLACE;
fmtlib=formats;
RUN;

Your suggestions is not convenient because i don't want to overwrite initial values by characters formats (i want to keep values and associated formats like i have in sas), it is impossible? 😕

Re: Export SAS dataset to STATA keeping the formats

ballardw — Tue, 03 Mar 2020 17:36:11 GMT

@Vincent5 wrote:

Thank you for your answer, i didn't know that.

Your suggestions is not convenient because i don't want to overwrite initial values by characters formats (i want to keep values and associated formats like i have in sas), it is impossible? 😕

I really did not suggest "overwrite initial values". I suggested creating additional new variables.

I don't use STATA, don't have access to STATA and can't really say what may be going on. I know that for some file formats the version SAS exports may not be native to the very latest release and so you may be able to tell the other program to treat the file as the version SAS exports. But I don't know if that is possible or how to do it. The documentation I looked at in SAS says that SAS supports STATA 12 and earlier versions.

Re: Export SAS dataset to STATA keeping the formats

Tom — Tue, 03 Mar 2020 19:22:05 GMT

What does your SAS log show?

It looks like told PROC FORMAT to store the formats in a catalog named FORMATS.FORMATS and you told PROC EXPORT to use the formats in a catalog named WORK.FORMATS. Make sure that you tell PROC EXPORT to use the catalog that actually has the format definitions.

Re: Export SAS dataset to STATA keeping the formats

Vincent5 — Wed, 04 Mar 2020 09:39:55 GMT

Thank you for your messages, i made the correction: "fmtlib = formats.formats" in the proc export but that didn't resolve the problem.

I noticed that even if i write anything like "fmtlib=aaaaa.bbbb" (doesn't exist), sas log show:

The export data set has 5 observations and 1 variables.
NOTE: "...\base.dta" file was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):

It seems that sas (or just sas log..) ignores fmtlib option.

Re: Export SAS dataset to STATA keeping the formats

Tom — Wed, 04 Mar 2020 21:42:11 GMT

Use the exact same two level name in the PROC FORMAT statement and the FMTLIB statement.

Make sure the catalog has the formats. Make sure the variables are numeric. Make sure the formats are simple 1-1 mappings.

Try this test and open the two files in STATA and see if it works. It looks to me like SAS has include value labels in the formats.dta file.

%let path=%sysfunc(pathname(work));

proc format lib=work.formats ;
 value sexf 0='MALE' 1='FEMALE' ;
run;
data test;
  set sashelp.class ;
  keep sexf;
  sexf=sex='F';
  format sexf sexf. ;
run;

proc export data=test
  outfile="&path/formats.dta" replace
;
  fmtlib=work.formats;
run;
proc export data=test
  outfile="&path/noformats.dta" replace
;
  fmtlib=zzzwork.formats;
run;
data _null_;
  infile "&path/formats.dta" lrecl=100 recfm=f obs=20;
  input;
  list;
run;

data _null_;
  infile "&path/noformats.dta" lrecl=100 recfm=f obs=20;
  input;
  list;
run;

When I dump the file as binary the end of the file looks like this:

5   CHAR  ..............................$...sexf........................................................MALE.F
    ZONE  0000000000000000000000000000002000767600000000000000000000000000000000000000000000000000000000444404
    NUMR  00000000000000000000000000000040003586000000000000000000000000000000002000C0000000500000001000D1C506

6   CHAR  EMALE. 6
    ZONE  444440
    NUMR  5D1C50

And the onw where I pointed it at a non-existent catalog the MALE FEMALE text is not in the file.

4   CHAR  ð?......ð?......................ð?......ð?......................ð?......ð?......ð?......ð?..........
    ZONE  F3000000F30000000000000000000000F3000000F30000000000000000000000F3000000F3000000F3000000F30000000000
    NUMR  0F0000000F00000000000000000000000F0000000F00000000000000000000000F0000000F0000000F0000000F0000000000

5   CHAR  .............................. 30
    ZONE  000000000000000000000000000000
    NUMR  000000000000000000000000000000

Re: Export SAS dataset to STATA keeping the formats

Vincent5 — Thu, 05 Mar 2020 10:55:17 GMT

Thank you, i've finally found the problem thanks for your help.

It's just that length of formats names should not exceed 8 characters (i imagine it's the maximum format name size in stata), and in my example, my format name is "nationalite" which has 11 characters.

So i just have to convert my characters variables into numerics for which i want to apply a format, and then reduce size (bellow 8 characters) of my formats names and it should work ! 🙂

Thank you

Re: Export SAS dataset to STATA keeping the formats

KJL — Tue, 17 Aug 2021 15:24:34 GMT

Has the issue been solved?

I have the same issue, and the lengths for my variable format names are not longer than 8.

Re: Export SAS dataset to STATA keeping the formats

BayzidurRahman — Wed, 16 Nov 2022 19:17:32 GMT

The fact that the exported datasets does not have any formats attached to a variable where format name is >8 Character long is not a problem of Stata. Because, I have checked that Stata 17 can create formats with name longer than 8 character and attach them to variables without any problems. I also replicated the SAS code to create a SPSS datafile which also has the same problem. Thus, it looks to me an issue with the FMTLIM statement in PROC EXPORT.