topic Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE in SAS Programming

ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

LMSSAS — Tue, 14 Dec 2021 20:15:54 GMT

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;

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Tom — Tue, 14 Dec 2021 20:24:11 GMT

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;

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

ErikLund_Jensen — Tue, 14 Dec 2021 20:57:20 GMT

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.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Tom — Tue, 14 Dec 2021 21:46:44 GMT

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.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

ballardw — Tue, 14 Dec 2021 21:51:11 GMT

@Tom

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;

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Tom — Tue, 14 Dec 2021 22:14:38 GMT

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.

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

LMSSAS — Wed, 15 Dec 2021 13:17:23 GMT

Tom, Thank you, this worked!!

I appreciate the help 🙂

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

LMSSAS — Wed, 15 Dec 2021 13:18:06 GMT

Thank you for your time !

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

LMSSAS — Wed, 15 Dec 2021 13:18:27 GMT

Thank you for your time!

Re: ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

LMSSAS — Wed, 15 Dec 2021 13:18:53 GMT

Thank you for your time!