topic Re: Exporting to Excel 2 rows of column names using SAS EG. in ODS and Base Reporting

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

sallyjo — Tue, 06 Mar 2018 00:17:30 GMT

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?

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

Cynthia_sas — Tue, 06 Mar 2018 01:10:23 GMT

Hi:

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

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

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

sallyjo — Tue, 06 Mar 2018 13:30:28 GMT

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 Label	Gender Label	Age Label	Height Label	Weight Label
Name Label	Gender Label	Age Label	Height Label	Weight Label
Alfred	M	14	69	112.5
Alice	F	13	56.5	84
Barbara	F	13	65.3	98
Carol	F	14	62.8	102.5
Henry	M	14	63.5	102.5
James	M	12	57.3	83
Jane	F	12	59.8	84.5
Janet	F	12	62.5	112.5

Alphabetic List of Variables and Attributes# Variable Type Len Label

3	Age	Num	8	Age Label
4	Height	Num	8	Height Label
1	Name	Char	25	Name Label
2	Sex	Char	1	Gender Label
5	Weight	Num	8	Weight Label

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

Cynthia_sas — Tue, 06 Mar 2018 15:52:07 GMT

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

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

sallyjo — Tue, 06 Mar 2018 18:04:44 GMT

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?

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

Cynthia_sas — Tue, 06 Mar 2018 18:32:14 GMT

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

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

sallyjo — Wed, 07 Mar 2018 15:13:21 GMT

Thanks, Cynthia!!