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?
Hi, take a look at this paper: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf
cynthia
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
This is Awesome Haikuo you are the Genius one shot Solution which gave ... really saved my day ... thanks a lot
Hi, take a look at this paper: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf
cynthia
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?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.