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
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=...
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=...
Highly appreciated for your proposal. I think the ODS EXCEL works for me at present.
Thanks would like to know if we import back the same file in SAS will it be same as dataset we exported into excel.
Hi, do bear in mind this topic was closed 6 years ago. Its a good idea to open new questions as things will have changed in that time.
In terms of will the data be the same when imported back, the answer is it will retain the data part, however anything SAS specific will not, so if you export:
A B C
1 2 3
You will get a dataset with columns named A,B,C, and one row of data 1,2,3, but you will not necessarily get the right length, format, informat. And also depending on language settings/special characters etc. the data might be different too.
This doesn't work for a large dataset, you receive an out of memory error.
@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
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.
@art297, I get these errors while running the macro:
MACROGEN(EXPORTXL): put m;
MACROGEN(EXPORTXL): rc=filename('code2inc');
MACROGEN(EXPORTXL): stop;
MACROGEN(EXPORTXL): end;
MACROGEN(EXPORTXL): run;
ERROR: Undetermined I/O failure.
MACROGEN(EXPORTXL): call system('cscript "P:\work\_TD8972_JAMES_\Prc2\PasteIt.vbs"' );
MACROGEN(EXPORTXL): run;
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?
Yes! The macro was designed to run on base SAS using a Windows operating system.
Art, CEO, AnalystFinder.com
I download the code from https://github.com/art297/Papers/blob/master/Excelling_to_Another_Level_with_SAS/src/exportxl.sas
Getting an error:
ERROR: Undetermined I/O failure.
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
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
I had the same issue. I was able to get around it by loading the variable with the custom format to another string variable...
data YOURDATA;
set YOURDATA;
format SWAP $50.;
SWAP = PUT(FORMATTED_VALUE, $CUSTOM_FORMAT.);
run;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.