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

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?                                            

1 ACCEPTED SOLUTION
5 REPLIES 5
Haikuo
Onyx | Level 15

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

vinayak_nakina
Calcite | Level 5

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

TedShelly
Calcite | Level 5

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?

Cynthia_sas
SAS Super FREQ

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

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
  • 5 replies
  • 12926 views
  • 3 likes
  • 4 in conversation