CODEWITHSUNDEEP
excelsasheaderods
Srividya krishnan
Srividya krishnan

Reputation: 25

Exporting SAS table with numeric varnames

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?

Upvotes: 0

Views: 952

Answers (3)

Reeza
Reeza

Reputation: 21264

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

Upvotes: 1

Richard
Richard

Reputation: 27498

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

Upvotes: 2

Tom
Tom

Reputation: 51566

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

Upvotes: 1

Related Questions