Exporting SAS table with numeric varnames

0
votes

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?

3
excelsasheaderods
Why are you writing an HTML file if you want an Excel file? – Tom
Hi Tom, I also tried writing it as an excel file, but it still doesn't let me write the names on SAS to excel as it is. – Srividya krishnan
Do you want the cells for the header to contain the numbers, but have an Excel display style that shows the leading zeros? Or do you want the cells to contain the strings 010 etc? If the later is it ok if the cells contain can extra character, like 'A0'x that is normally invisible? – Tom
I would want the header on the excel sheet for the column to display 010,020 instead of 10,20. So, it the leading zero on the column names and I want those headers to be in text format – Srividya krishnan
Excel is changing the format. When you used PROC EXPORT did you use the labels options? If not, try that. – Reeza

3 Answers

2
votes

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;

enter image description here

1
votes

One way is to generate a CSV and then open the CSV file using the "From Text/CSV" option in the "DATA" menu of Excel. You will have to edit the transformations that it does to the file to keep Excel from converting the header rows into numbers. When I tried it with a simple example I had to manually tell it that I had header rows and then remove the type conversion it did that changed the header row into numbers. Then I had to add back transformation to convert the actual values from text to numbers.

data check;
  x1=200;
  x2=500;
  x3=1000;
  label x1='010' x2='020' x3='030';
run;

ods csv file='c:\downloads\check.csv';
proc print noobs label data=check;
run;
ods csv close;

enter image description here

You could also add some non-space, non-digit character (such as 'A0'x or other non-printing character) into the header strings and then Excel will not convert them into numbers. In that case you can use ODS EXCEL as your output and write XLSX file directly.

data check;
  x1=200;
  x2=500;
  x3=1000;
  * Last character in labels is 'A0'x ;
  label x1='010 ' x2='020 ' x3='030 ';
run;

ods excel file='c:\downloads\check.xlsx';
proc print noobs label data=check;
run;
ods excel close;

enter image description here

1
votes

Proc export with labels works for me - SAS 9.4 TS1M5

    data have;
    set sashelp.class;

    label age = '010';
    label sex = '021';
    label name = '030';
    label weight = '000';
    label height = '245';

    run;

    proc export data=have outfile='/home/fkhurshed/Demo1/delete1.xlsx' dbms=xlsx label; run;

enter image description here