Help using Base SAS procedures

Output data to Excel

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

Output data to Excel

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.


Accepted Solutions
Solution
‎11-08-2011 10:18 AM
Frequent Contributor
Posts: 131

Output data to Excel

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;

View solution in original post


All Replies
PROC Star
Posts: 7,487

Output data to Excel

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.

Frequent Contributor
Posts: 131

Output data to Excel

A bit paiful, indeed.

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

Solution
‎11-08-2011 10:18 AM
Frequent Contributor
Posts: 131

Output data to Excel

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;

Super Contributor
Posts: 279

Output data to Excel

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

Frequent Contributor
Posts: 131

Output data to Excel

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

Super Contributor
Posts: 279

Output data to Excel

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;

Super User
Super User
Posts: 7,074

Output data to Excel

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 198 views
  • 6 likes
  • 4 in conversation