ODS EXCEL DROPPING LEADING ZEROES ON CHARACTER VARIABLE

[LMSSAS]

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;

[Tom]

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;

[Tom]

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.

[Tom · Accepted solution]

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.

[LMSSAS]

Tom, Thank you, this worked!!

I appreciate the help 🙂

[LMSSAS]

Thank you for your time!

[ballardw]

@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;

---

[LMSSAS]

Thank you for your time!

[LMSSAS]

Thank you for your time !

[ErikLund_Jensen]

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.