Exporting SAS table with numeric varnames

Question

I'm trying to export a sas dataset which has numeric column names (eg, 010,020,030) like index. When I print this dataset the results tab displays them in same format. When I export the file using ODS/proc export, excel automatically changes the column names to 10,20,30 etc,. Is there a way to maintain the column headers as text.

I've tried ODS HTML and proc export. I also tried exporting the dataset as xml and tried opening it in excel. But Excel automatically changes it to a number.

data check;
'010'n=200;
'020'n=500;
'030'n=1000;
run;

options missing=0;
ODS HTML FILE="&output_loc./check.xls";
Title "check col names";
proc print data=check noobs style={textalign=left};run;
Title;
ODS HTML CLOSE;
options missing=.;

I expect the column names on the excel sheet to be 010,020,030. Can somebody help me on this?

Answer 1

Use the ODS style option pretext= to prepend a hard-space 'A0'x to the header cell values. The hard-space will prevent Excel from interpreting numeric string values as general numeric.

You can also (per @Tom) use ODS style option tagattr='type:text'. See Insights from a SAS Technical Support Guy: A Deep Dive into the SAS® ODS Excel Destination Chevell Parker, SAS Institute Inc. for more tagattr info.

options validvarname=any;

data have;
'010'n=200;
'020'n=500;
'030'n=1000;
run;

ods _all_ close;

ods excel file='c:\temp\number-name-games.xlsx';

proc print data=have 
  style(header)=[pretext="A0"x]
/*
  style(header)=[tagattr='type:text']
*/
;
run;

ods excel close;