The SAS Output Delivery System and reporting techniques

export to excel with special needs

Reply
Frequent Contributor
Posts: 91

export to excel with special needs

Hello friends,

I have 5 SAS tables.

I want to export them to one excel file as following:

Tables Pop1 ,Pop2,Pop3  to have in sheet1 (side by side to each other)

Tables Ref1  ,Ref2 to have in sheet2 (side by side to each other)

Please mention that I want all tables to be in one excel file (with two sheets as I explained before)

 

 

 

 

Data pop1;
input x;
cards;
1
2
;
Run;

Data pop2;
input x;
cards;
3
4
;
Run;

Data pop3;
input x;
cards;
5
6
;
Run;

Data Ref1;
input w;
cards;
10
20
;
Run;


Data Ref2;
input x;
cards;
30
40
;
Run;

 

%macro mmacro1;
%do k=1 %to 3;
Title "pop&k.";
proc print data=pop&k. noobs;
footnote;
Run;
%end;
%mend;
%mmacro1;


%macro mmacro2;
%do k=1 %to 2;
Title "Ref&k.";
proc print data=Ref&k. noobs;
footnote;
Run;
%end;
%mend;
%mmacro2;

Frequent Contributor
Posts: 91

Re: export to excel with special needs

If I run this code I get each table in a different sheet!!

 

ods tagsets.excelxp
file="/usr/local/SAS/MidulOld/UserDir/Ron.xls"
options(sheet_name='Pop') style=sasweb;
%macro mmacro1;
%do k=1 %to 3;
Title "pop&k.";
proc print data=pop&k. noobs;
footnote;
Run;
%end;
%mend;
%mmacro1;

ods tagsets.excelxp options(sheet_name='Ref');
%macro mmacro2;
%do k=1 %to 2;
Title "Ref&k.";
proc print data=Ref&k. noobs;
footnote;
Run;
%end;
%mend;
%mmacro2;
ods _all_ close;

Super User
Posts: 9,549

Re: export to excel with special needs

[ Edited ]

You might be able to achieve something like this with using named ranges, but I'd rather create a dataset for each sheet and then copy it into the Excel file with libname excel, or use tagsets.excelp to print each dataset.

 

Use a simple datastep merge to put the values side-by-side (you can't use the same column name, of course, but you can use labels for the names):

data sheet1
merge
  pop1
  pop2 (rename=(x=x2))
  pop3 (rename=(x=x3))
;
label
  x = 'x'
  x2 = 'x'
  x3 = 'x'
;
run;

data sheet2;
merge
  ref1
  ref2
;
run;

Then, in your ods output, just do

ods tagsets.excelxp
file="/usr/local/SAS/MidulOld/UserDir/Ron.xls"
options(sheet_name='Pop') style=sasweb;

proc print data=sheet1 label noobs;
run;

ods tagsets.excelxp options(sheet_name='Ref');

proc print data=sheet2 noobs;
run;

ods tagsets.excelxp close;

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: export to excel with special needs

Posted in reply to KurtBremser

Thank you so much and it was a good answer.

But....I want to have separate tables and add also titles  for each table.

So the merge idea is not good .

I want to have a panel of separate tables in same sheet (3 tables in sheet1 and 2 tables in sheet2)

 

Ron

Ask a Question
Discussion stats
  • 3 replies
  • 150 views
  • 0 likes
  • 2 in conversation