BookmarkSubscribeRSS Feed
sallyjo
Calcite | Level 5

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?

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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

sallyjo
Calcite | Level 5

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
Cynthia_sas
SAS Super FREQ
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
sallyjo
Calcite | Level 5

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?

Cynthia_sas
SAS Super FREQ

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

 

 

 

 

 

sallyjo
Calcite | Level 5

Thanks, Cynthia!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1853 views
  • 0 likes
  • 2 in conversation