The SAS Output Delivery System and reporting techniques

Exporting to Excel 2 rows of column names using SAS EG.

Reply
New Contributor
Posts: 4

Exporting to Excel 2 rows of column names using SAS EG.

How do I export to excel, Row1 with the label names, Row 2 with the SAS variable names, and Rows3 - Rows n the data, which can be numeric or text depending on the column?

SAS Super FREQ
Posts: 9,371

Re: Exporting to Excel 2 rows of column names using SAS EG.

Hi:

  You can't use PROC EXPORT to do it, but you can do it with PROC REPORT and writing code:

two_row_excel.png

 

PROC REPORT allows spanning headers in the COLUMN statement,where you can put the labels you want and that will allow the variable names to be in row 2.

 

Here's the code using ODS EXCEL:

ods excel file='c:\temp\two_row_head.xlsx';
proc report data=sashelp.class;
column ('Name Label' name) ('Gender Label' sex) ('Age Label' age)
       ('Height Label' height) ('Weight Label' weight);
define name / order;
define sex / display;
define age / display;
define height / display;
define weight / display;
run;
ods excel close;

cynthia

New Contributor
Posts: 4

Re: Exporting to Excel 2 rows of column names using SAS EG.

Posted in reply to Cynthia_sas

Cynthia,

 

Thanks for your timely response!  I used your data and code, my excel exported two rows of label names.  Can you see what I did incorrectly?

ODS EXCEL FILE = '/Informatics_Customers/GP/GP/Temp/saj/Sample_ODS_Excel.xlsx';

PROC REPORT DATA=Sample_ODS_Excel;

COLUMN ('Name Label' Name)

('Gender Label' Sex )

('Age Label' Age)

('Height Label' Height)

('Weight Label' Weight);

DEFINE Name / Order;

DEFINE Sex / DISPLAY;

DEFINE Age / DISPLAY;

DEFINE Height / DISPLAY;

DEFINE Weight / DISPLAY;

RUN;

ODS EXCEL CLOSE;

 

Name LabelGender LabelAge LabelHeight LabelWeight Label
Name LabelGender LabelAge LabelHeight LabelWeight Label
AlfredM1469112.5
AliceF1356.584
BarbaraF1365.398
CarolF1462.8102.5
HenryM1463.5102.5
JamesM1257.383
JaneF1259.8

84.5

JanetF1262.5112.5
Alphabetic List of Variables and Attributes# Variable Type Len Label
3AgeNum8Age Label
4HeightNum8Height Label
1NameChar25Name Label
2SexChar1Gender Label
5WeightNum8Weight Label
SAS Super FREQ
Posts: 9,371

Re: Exporting to Excel 2 rows of column names using SAS EG.

Hi:
You did not submit the same code. My program used SASHELP.CLASS. Your program used Sample_ODS_Excel -- please run my exact program using SASHELP,CLASS and you should see my SAME results. If I had to guess, I would guess that when you created the test dataset, you had a LABEL statement in your code. So the labels got permanently assigned to the data.

In your case, this is NOT what you want. You do NOT want permanent labels assigned to the data because PROC REPORT uses labels by default. When I used SASHELP.CLASS, I did it because I know that the data does NOT have any permanent labels.

Please run the code as I posted it.

cynthia
New Contributor
Posts: 4

Re: Exporting to Excel 2 rows of column names using SAS EG.

Posted in reply to Cynthia_sas

Thanks!  I appreciate you timely response to my questions.  Do you know if there's a  book I can buy so that I can learn all the options available to me with ODS and excel?

SAS Super FREQ
Posts: 9,371

Re: Exporting to Excel 2 rows of column names using SAS EG.

Hi:

  Before you buy a book, I recommend searching for user group papers on ODS on the www.lexjansen.com web site -- there's an index of over 30,000 user group papers indexed on the web site. And the papers are free to you.

 

  I have a few papers that I can recommend:

http://support.sas.com/resources/papers/proceedings13/366-2013.pdf (there's a long reference section at the end of this paper)

http://support.sas.com/resources/papers/proceedings11/300-2011.pdf

http://www2.sas.com/proceedings/forum2008/173-2008.pdf

http://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf

http://www2.sas.com/proceedings/forum2007/099-2007.pdf

 

  And if you want to buy a book, there are a few by Art Carpenter on PROC REPORT and by Lake and McKnight on PROC TABULATE that include using ODS with those procedures. And I have a book with Lauren Haworth Lake and Michele Burlew on ODS.

 

Cynthia

 

 

 

 

 

Highlighted
New Contributor
Posts: 4

Re: Exporting to Excel 2 rows of column names using SAS EG.

Posted in reply to Cynthia_sas

Thanks, Cynthia!!

Ask a Question
Discussion stats
  • 6 replies
  • 187 views
  • 0 likes
  • 2 in conversation