BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hello,

In SAS 9.1 how to export to excel, values more than 16 digits with leading zeros and create multiple worksheets in the same excel file

So in SAS 9.1 how can I get the two requirements fullfilled
1. Able to export 20 digit values to excel with leading zeros
2. Create multiple worksheets in the same excel file.

The below code is used to create excel file with values having 20 digit numbers and leading zeros. This works fine in 9.2 for ODS Msoffice2k and ODS tagsets.ExcelXP. However in 9.1 only the Msoffice2k code works fine.


data one;
input account name $;
cards;
01234567890123456789 Bob
054556 Henry
034456 Wes
;
run;

/* ExcelXP destination */
ods tagsets.ExcelXP file="c:\temp.xls";
proc print data=one;
var name;
var account / style(data)={tagattr="format:@"};
format account z20.;
run;
Ods tagsets.ExcelXP close;


/* MSOffice2k destination */
ods Msoffice2k file="c:\temp1.xls";
proc print data=one;
var name;
var account / style(data)={htmlstyle="mso-number-format:\@"};
format account z20.;
run;
Ods msoffice2k close;

Regards
Sanjay
1 REPLY 1
Cynthia_sas
SAS Super FREQ
Hi:
In this previous forum posting:
http://support.sas.com/forums/thread.jspa?messageID=2010ߚ

the poster was using TAGSETS.EXCELXP and SAS 9.1.3 and successfully got his credit card numbers rendered as text.

I wonder whether you have the correct tagset template for the ExcelXP tagset installed. In the posting, it was the ExcelXP v1.37 tagset that gave the correct results. If you are not using this tagset template version number to create your output, you may wish to work with Tech Support to find out what the current tagset template number is and how to install it to see whether it gives the same results as the v1.37 tagset template.

This web site has the updated tagset templates:
http://support.sas.com/rnd/base/topics/odsmarkup/ and this Tech Support note explains how to update the tagset template item store.
http://support.sas.com/kb/32/394.html

cynthia

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1 reply
  • 1733 views
  • 0 likes
  • 2 in conversation