Help using Base SAS procedures

How to retain the Variable Format when PROC EXPORT TO EXCEL

Reply
Frequent Contributor
Posts: 88

How to retain the Variable Format when PROC EXPORT TO EXCEL

Dear All, 

 

I am facing an frustrating problem: when I use PROC EXPORT to export data (with many variables have format ) to EXCEL, I found the format is lost, e.g. SEX only appear 1 or 2  in EXCEL.

 

I have searched in our community, but no easy way. Like ODS EXCEL+PROC REPORT, since many variable have their formats, then it is not a fun thing to define each variable.

 

May I know if there is an easy way? 

 

Best,
Jack

Super User
Super User
Posts: 7,955

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

[ Edited ]

It is not possible with proc export to retain formats.  It is a plain data dump to Excel - and then you get all the "features" of Excel like dropping preceding zeros and such like.  If you are creating a report and it needs to have special formatting and such like then you need to write the code to do this.  Up to now I would use ods tagsets.excelxp and proc report, however the ods excel now creates native XLSX files, so best to go with that if you can.  This should also retain the formatted value, for example:

proc format;
  value $sex
    "M"="Male"
    "F"="Female";
run;

data want;
  set sashelp.class;
  format sex $sex.;
run;

/* Note for older versions of sas you could use
ods tagsets.excelxp file="c:\test.xml";
To get the same result */
ods excel file="c:\test.xlsx"; proc report data=want; columns _all_; run; ods excel close;

If you want special formatting from Excel then add a row for each column that needs it and use define / style=...

Frequent Contributor
Posts: 88

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Highly appreciated for your proposal. I think the ODS EXCEL works for me at present. 

PROC Star
Posts: 7,473

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

@Jack2012: There is an easy way. Download and run the free macro at: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Then, the following example will do exactly what I think you said you want to do:

proc format;
  value $gender
  'M'='Male'
  other='Female'
  ;
  
  value age
  low-13='young'
  other='not as young'
  ;
run;

data class;
  set sashelp.class;
  format sex $gender.;
  format age age.;
run;

%exportxl(data=class,
                outfile=c:\art\class.xlsx,
                useformats=Y)

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 88

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

Thanks Art, the problem, after I read the code in the link, is the format of my data is not stored in the DICTIONARY library, actually there is no such library. I am lost which one to use as instead of DICTIONARY since I have no idea about the such library. 

PROC Star
Posts: 7,473

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

The metadata for ALL SAS datasets is stored in the various dictionary files .. NO EXCEPTIONS! The files are automatically built and maintained by the software.

 

Art, CEO, AnalystFinder.com

 

SAS Super FREQ
Posts: 304

Re: How to retain the Variable Format when PROC EXPORT TO EXCEL

You can run the code below to see some of the table metadata:

 

proc format;
  value $gender 'M'='Male'
                other='Female';
  
  value age low-13='young'
            other='not as young';
run; quit;

data work.class;
set sashelp.class;
format sex $gender.;
format age age.;
run;

title 'SASHELP.VCOLUMN - PROC PRINT';

proc print data=sashelp.vcolumn noobs label;
  where (libname eq 'WORK' and memname eq 'CLASS');
  var name type length format;
run; quit;

title 'DICTIONARY.COLUMNS - PROC SQL';

proc sql;
  select name, type, length, format
  from dictionary.columns
  where (libname eq 'WORK' and memname eq 'CLASS');
quit;  

 

Vince DelGobbo

SAS R&D

Ask a Question
Discussion stats
  • 6 replies
  • 1955 views
  • 2 likes
  • 4 in conversation