BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bncoxuk
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
bncoxuk
Obsidian | Level 7

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

7 REPLIES 7
art297
Opal | Level 21

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
Obsidian | Level 7

A bit paiful, indeed.

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

bncoxuk
Obsidian | Level 7

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
Pyrite | Level 9

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

bncoxuk
Obsidian | Level 7

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

GreggB
Pyrite | Level 9

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
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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