The SAS Output Delivery System and reporting techniques

Maintain leading zeros in ExcelXP output

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Maintain leading zeros in ExcelXP output

I use the ExcelXP tagset frequently.  One thing I haven’t been able to resolve is how to stop Excel from dropping leading zeros. I have a data variable that is defined as character but always contains a number.  If the number has leading zeros I want to display them in a spreadsheet I create with the ExcelXP tagset.  But Excel always drops the leading zeros.  How can I accomplish this?                                            


Accepted Solutions
Solution
‎09-16-2013 03:44 PM
SAS Super FREQ
Posts: 8,868

Re: Maintain leading zeros in ExcelXP output

Posted in reply to TedShelly

All Replies
Respected Advisor
Posts: 3,156

Re: Maintain leading zeros in ExcelXP output

Posted in reply to TedShelly

Something along the line with the following may help:

ods _ALL_ close;

ODS Tagsets.ExcelXP file="test.xml"

/*style=analysis*/

options(absolute_column_width='10,30');

PROC print DATA=TEST;

var  ZERO_LEADING_VAR / style={tagattr='Format:@'};

var  /*OTHER VARS*/;

RUN;

ODS Tagsets.ExcelXP CLOSE;

ods listing;

Haikuo

New User
Posts: 1

Re: Maintain leading zeros in ExcelXP output

This is Awesome Haikuo you are the Genius one shot Solution which gave ... really saved my day ... thanks a lot

Solution
‎09-16-2013 03:44 PM
SAS Super FREQ
Posts: 8,868

Re: Maintain leading zeros in ExcelXP output

Posted in reply to TedShelly
Occasional Contributor
Posts: 10

Re: Maintain leading zeros in ExcelXP output

Posted in reply to Cynthia_sas

Thanks for both replies.  They were helpful.

Can I apply this to PROC SQL and PROC FREQ?  Or does it only work for PROC PRINT?

SAS Super FREQ
Posts: 8,868

Re: Maintain leading zeros in ExcelXP output

Posted in reply to TedShelly

Hi:

  PROC SQL and PROC FREQ are harder. With PROC SQL, I would suggest that you use CREATE TABLE and then do a PROC PRINT or PROC REPORT on the SQL table. With PROC FREQ, I would suggest that you create a output dataset from the procedure and then do a PROC PRINT or a PROC REPORT on the FREQ output. Or, if you are getting percents from PROC FREQ, consider using PROC TABULATE to generate the N and PCTN statistics and use STYLE overrides, instead of using PROC FREQ.

  The techniques shown in my paper (STYLE overrides in the procedure syntax) will work with PROC PRINT, PROC REPORT and PROC TABULATE. For other SAS procedures, you either need to alter the TABLE template used by the procedure or you need to create a dataset from the procedure and then pass the dataset using PROC PRINT, PROC REPORT and PROC TABULATE. It is possible to alter the TABLE template used by the SAS procedure (such as PROC FREQ or PROC MEANS or PROC GLM), but you have to learn PROC TEMPLATE syntax for TABLE templates, and specifically, for the TABLE template used by your procedure of choice. This is not something to be undertaken lightly. Here's an example of changing a PROC FREQ template - -slightly different changes, but same concept (25058 - Changing the format of one-way table output in PROC FREQ ).

  The easier solution is to use the OUT= or OUTPUT features of the procedure or ODS OUTPUT or CREATE TABLE to make a dataset and then use PROC PRINT or PROC REPORT. If you decide that you really want to alter the table template for PROC FREQ and/or PROC SQL, then I would recommend working on this task by opening a track with SAS Tech Support (especially if you have no prior experience with TABLE templates).

cynthia

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 6806 views
  • 3 likes
  • 4 in conversation