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

Hello,

Hoping to find some help with the following code:

data x;

set y;

SNum=(put(input(SNum,6.),Z6.));

run;

ods excel file="path.xls";

proc print data=x label noobs;

format DVisit date.;

run;

ods excel close;

When I run the code, SAS in the Output window shows the six digit "SNum", but when it's exported to Excel "SNum" is no longer padded to 6 digits. What gives?

When I try to format the SNum in the PROC PRINT step, SAS gives me the following error:

ERROR: You are trying to use the numeric format Z with the character variable SNum in data set WORK.X.

I thought by having this in the DATA step, the format becomes permanent.

How do I fix this?

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi: TAGATTR will only work with TAGSETS.EXCELXP. I do not believe it works with ODS EXCEL. The original post showed ODS EXCEL. I understand that by later versions of SAS 9.4 (not M0), the SAS formats (such as Z6.) would be automatically used by ODS EXCEL. But the original post did not indicate the version of SAS or whether ODS EXCEL was a typo or whether he meant ODS TAGSETS.EXCELXP. So if he did mean the new ODS EXCEL, then he should be able to use the format directly without creating the dataset at all. The code shown in the attached screen shots was run by someone in Tech Support who had access to multiple versions of SAS 9.4 and even though there are minor differences, the pictures show the Z format being used, without the need for a DATA step program and explicit conversion.

cynthia


code_for_new_M1_xlsx.pngcode_for_new_xlsx.pngpic_of_new_m1_xlsx.pngpic_of_new_xlsx.png

View solution in original post

9 REPLIES 9
SASKiwi
PROC Star

Open a brand-new Excel spreadsheet and in any cell type in the numbers 000111, then press enter. What you will see is not 000111 but just 111. This is the default Excel behaviour. This is exactly what Excel is doing when you open your spreadsheet created from PROC PRINT. It  does not matter if your SAS column is numeric or character or what SAS format you apply you will get the same result.

To fix the problem you have provide Excel with some further formatting instructions to avoid the default behaviour.

Try adding the following VAR statement to your PROC PRINT:

var snum / style(column)={TAGATTR="Format:000000"};

dereck255
Fluorite | Level 6

Thank you for your reply, though It didn't work. Instead, I got a second column labeled "snum" with the same unpadded digits. Any other ideas?

MaikH_Schutze
Quartz | Level 8

Hi,

What version of SAS are you using? There is a pre-prodution tagset in 9.4 that should automatically convert SAS formats to Excel formats without the TAGATTR= option.

And maybe I don't understand what you are trying to do. It looks like you are explcitily converting a character field to a numeric field with the w.d format - input(SNum,6.) - and then do another explicit conversion back to character using Zw.d function in the PUT statement.

Does your the original value for SNum already have leading zeroes? Maybe a few lines are data would help so we can test the logic.

SASKiwi
PROC Star

Try: var snum / style(column)={TAGATTR="Format:@"};

Cynthia_sas
SAS Super FREQ

Hi: TAGATTR will only work with TAGSETS.EXCELXP. I do not believe it works with ODS EXCEL. The original post showed ODS EXCEL. I understand that by later versions of SAS 9.4 (not M0), the SAS formats (such as Z6.) would be automatically used by ODS EXCEL. But the original post did not indicate the version of SAS or whether ODS EXCEL was a typo or whether he meant ODS TAGSETS.EXCELXP. So if he did mean the new ODS EXCEL, then he should be able to use the format directly without creating the dataset at all. The code shown in the attached screen shots was run by someone in Tech Support who had access to multiple versions of SAS 9.4 and even though there are minor differences, the pictures show the Z format being used, without the need for a DATA step program and explicit conversion.

cynthia


code_for_new_M1_xlsx.pngcode_for_new_xlsx.pngpic_of_new_m1_xlsx.pngpic_of_new_xlsx.png
SASKiwi
PROC Star

Oops. Cynthia is quite correct. I should have looked more closely at the original post...

MaikH_Schutze
Quartz | Level 8

Hi,

Thank you, Cynthia. Sadly, I still work on SAS 9.2 and I noticed the original post used the ODS Excel destination but I couldn't find anything in the documentation about TAGATTR and ODS Excel, although I didn't look that hard, but I did recently have a conversation with Chevell Parker and he had mentioned the new functionality for the forthcoming 9.4M4 release.

Thanks,

M.

Cynthia_sas
SAS Super FREQ

Hi:

  TAGATTR style override method of passing a Microsoft format to Excel was explicitly designed for use with the TAGSETS.EXCELXP destination, which creates a Spreadsheet Markup Language XML file that Excel knows how to open and render. This is entirely a different kind of file than the XLSX zip archive file created by the new (SAS 9.4) ODS Excel. Many of the format issues that required the use of TAGATTR with TAGSETS.EXCELXP will not be an issue with ODS EXCEL. Since the original posting showed ODS EXCEL, then I wanted to clarify that if the original post was indeed about ODS EXCEL in SAS 9.4, then a conversion to get leading zeroes should not be necessary (as shown in the screen shots that I attached).

  But whether the original poster is using ODS TAGSETS.EXCELXP or ODS EXCEL, there is no need to have a DATA step do the conversion to get leading zeroes, since both destinations have a way to handle that situation -- ODS TAGSETS.EXCELXP has an "external" style override method and ODS EXCEL has an internal method.

cynthia

dereck255
Fluorite | Level 6

This works perfectly! Thank you for your help!

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