Output data to Excel

[bncoxuk]

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.

[art297]

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.

[bncoxuk]

A bit paiful, indeed.

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

[bncoxuk · Accepted solution]

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;

[GreggB]

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

[bncoxuk]

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

[GreggB]

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;

[Tom]

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;