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!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.