topic Output data to Excel in SAS Procedures

Output data to Excel

bncoxuk — Tue, 08 Nov 2011 14:31:49 GMT

Hello,

I have a SAS dataset with a variable called region. The values are character type, as '01', '02', '03', ... '10', '11', '12'.

When I export the SAS dataset to Excel, the values in Excel changed to 1, 2, 3, ... 10, 11, 12. That is, the first digit 0 is missing.

My question is how to keep the values as in the original format '01', '02'...... When output to Excel?

Thanks.

Output data to Excel

art297 — Tue, 08 Nov 2011 14:37:09 GMT

One way is to export to an XML file. Take a look at: http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf

A bit painful, but it does let you control such formatting issues.

Output data to Excel

bncoxuk — Tue, 08 Nov 2011 14:45:54 GMT

A bit paiful, indeed.

Just wondering is there any simple way to do that? Simply just keep the format as original.

Output data to Excel

Tom — Tue, 08 Nov 2011 15:17:54 GMT

Can you convert it to a character variable?

If you use the EXCEL libname engine then it will preserve the leading zeros in strings that look like numbers.

libname test excel 'c:\temp\one.xls' ;

data test.one;

do i=1 to 10; c=put(i,z2.); output; end;

format i z2.;

run;

libname test clear;

Output data to Excel

bncoxuk — Tue, 08 Nov 2011 15:18:14 GMT

ODS LISTING CLOSE;

ODS tagsets.ExcelXP FILE="params.xml" PATH="Y:\" STYLE=Statistical;

PROC PRINT DATA=work.params02;

VAR level / STYLE={tagattr='format:text'};

VAR estimate stderr tvalue probt variable;

RUN;

ODS tagsets.ExcelXP CLOSE;

ODS LISTING;

Output data to Excel

GreggB — Tue, 08 Nov 2011 15:26:37 GMT

are you using PROC EXPORT or the EXPORT FILE under the FILE drop down menu?

Output data to Excel

bncoxuk — Tue, 08 Nov 2011 16:32:23 GMT

Initially, I used the EXPORT FILE under the FILE drop down menu.

Output data to Excel

GreggB — Tue, 08 Nov 2011 17:09:45 GMT

I use something like this. If you use the RETAIN statement it will force the vars to stay in the order you want them. Notice the RETAIN statement comes before the SET statement.

data final;

retain schoolid school Student_Num Last First Middle Grade Engprof Ethnicity Sex ;

set joinall;

school=put(schoolid,psfmt.);

keep school EngTch Engprof Ethnicity First GTclass GTmark GTtch Grade IEP LangRIT Last MathRIT MathTch Middle ReadRIT SciTch Sex ;

run;

proc export data=final outfile='G:\Departments\Research\MAP\1112\MAP - 1stQ_Grades - PASS\Fall MAP 1stQ Grades PASS - school..xls'

replace;

run;