I have data that I am exporting from SAS to Excel for our data managers. There are a couple of issues that I am noticing that I cannot correct. I want to see if there is a way to change this in SAS instead of manually doing it in Excel.
First, we have site numbers that are 3-digit character values, usually containing leading zeros. For example, site 001, 002, 003, etc. When it exports into Excel, it displays as numeric 1, 2, 3, etc. Is there a way to keep the leading zeros in Excel? If it can be retained as a character value, that would be ideal, but not necessary if we can at least keep the leading zeros.
FYI, I have tried adding to the SAS variable an apostrophe since Excel uses an apostrophe to treat a numeric value as a character string, but that does not work.
Second, I have long ID variables containing values like "5086074278593750", but they display in Excel as "5.08607E+15". I want to ignore the scientific notation and display it as the raw value. I can manually change the format in Excel from general to number and then remove the 2 decimal places, but I don't want to have to do that every time to every variable that is reformated.
In short, I want my data to show up in Excel exactly as I have it in SAS. Not format changes. Any solutions would be much appreciated!
As Reeza pointed out use TAGATTR. proc report...... define x /style={tagattr='format:text'}
HOW are you exporting to Excel?
Are you exporting a data set, the result of a report procedure (Procs Print, Report and Tabulate) or something else?
Different content is likely to require different options.
I am typically using ods tagsets.excelxp to export a table through PROC PRINT or PROC REPORT (usually PROC PRINT).
Here's an index of the features you want and where you can find sample code that implements that feature.
http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf
Leading zeroes is halfway down the page.
As Reeza pointed out use TAGATTR. proc report...... define x /style={tagattr='format:text'}
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.