I have a SAS data set that I'm exporting to excel using ODS and it is dropping the leading zeros. Below is my code, I am not getting an error but the data is still dropping the zeros, as if it isn't recognizing the code. The variable I am working with trying to keep the leading zeros is "Group" and even though it is numbers it is a Char in SAS.
ods excel file="/prod/SalesOperations/appdata/shared/Blue_Vision_Master_Renewal.xlsx" options (autofilter='all' sheet_name="Current_Month_Renewals");
ods excel options(sheet_name="RBMS_Health" autofilter='all');
proc report data=RBMS_BVMR;
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='Format:@'];
run;
ods excel options(sheet_name="Dental" autofilter='all');
proc report data=RBMS_HPS_DENTAL_FINAL;
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='Format:@'];
run;
ods excel options(sheet_name="Vision" autofilter='all');
proc report data=RBMS_HPS_VISION_FINAL;
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='Format:@'];
run;
ods excel options(sheet_name="Life" autofilter='all');
proc report data=Life;
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='Format:@'];
run;
ods excel close;
Found the issue. https://support.sas.com/kb/57/620.html
It seems that ODS EXCEL is ignoring the format:text tag. Instead you need to set type=String.
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='type:String']
;
As to why attaching a format to the variable and/or report column makes any difference I am still not sure. It must be that somehow PROC REPORT is for whatever random reason changing the TYPE it sets for the cell to string only when you have a format attached the variable (either in the source dataset or in the DEFINE statement) even if the variable is a character variable.
Try adding a FORMAT= option to your DEFINE statement.
Not sure why it works.
data test;
id = put(_n_,z5.);
set sashelp.class;
run;
ods excel file='c:\downloads\zeros.xlsx'
options
(autofilter='all' sheet_name="Current_Month_Renewals")
;
proc report data=test;
define id / display Group format=$5. style(Column)=[font_size=8pt width=.7in tagattr='Format:@'];
run;
ods excel close;
This really seems like a bug in PROC REPORT and/or ODS EXCEL.
Just attaching the $ format to a character variable causes the cell to be properly configured in EXCEL as containing text instead of numbers.
Found the issue. https://support.sas.com/kb/57/620.html
It seems that ODS EXCEL is ignoring the format:text tag. Instead you need to set type=String.
Define Group / display Group
style(Column)=[font_size=8pt width=.7in tagattr='type:String']
;
As to why attaching a format to the variable and/or report column makes any difference I am still not sure. It must be that somehow PROC REPORT is for whatever random reason changing the TYPE it sets for the cell to string only when you have a format attached the variable (either in the source dataset or in the DEFINE statement) even if the variable is a character variable.
Thank you for your time!
My guess is that with the explicit character format=$5.means when SAS creates the output it knows that the result sets the variable type in the xml hidden to the proper type in the XLSX file. Otherwise a "general" field is likely created for the Excel regardless of variable type, which will treat values like "000123" as a numeric 123. The question may be why the default Format is insufficient to do the same thing unless the default format for the OP is a custom format.
I vaguely remember a solution to an ODS EXCELXP question that was similar where the issue was regardless of the TAGATTR format the type that appeared in the created XML was incorrect. I don't recall the specific syntax but a type:'string' or similar had to be added to the TAGATTR options.
@Tom wrote:
Try adding a FORMAT= option to your DEFINE statement.
Not sure why it works.
data test; id = put(_n_,z5.); set sashelp.class; run; ods excel file='c:\downloads\zeros.xlsx' options (autofilter='all' sheet_name="Current_Month_Renewals") ; proc report data=test; define id / display Group format=$5. style(Column)=[font_size=8pt width=.7in tagattr='Format:@']; run; ods excel close;
Hi @LMSSAS
I had a lot of trouble with that too. In Denmark Socia Security Numbers are 10 digits, first digit is often a zero, and it is stored as Char in SAS.
I didn't succeded in preserving the leading zero until I discovered that it works if the SSN is converted to a Numeric variable and given the format Z10.
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.