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?
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
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 |
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 |
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?
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
Thanks, Cynthia!!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.