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;
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;
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;
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.