05-09-2017 05:29 AM
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?
05-09-2017 05:42 AM - edited 05-09-2017 06:00 AM
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=...
05-09-2017 03:08 PM
@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
05-10-2017 09:35 PM
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.
2 weeks ago
@art297, I get these errors while running the macro:
MACROGEN(EXPORTXL): put m;
ERROR: Undetermined I/O failure.
MACROGEN(EXPORTXL): call system('cscript "P:\work\_TD8972_JAMES_\Prc2\PasteIt.vbs"' );
ERROR: Shell escape is not valid in this SAS session.
Does my admin need to allow me to run the VBS via shell or something?
05-10-2017 09:49 PM
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
05-10-2017 10:33 PM
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;
03-05-2018 01:45 PM
I had the same issue. I was able to get around it by loading the variable with the custom format to another string variable...
format SWAP $50.;
SWAP = PUT(FORMATTED_VALUE, $CUSTOM_FORMAT.);