BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;

3 REPLIES 3
Ronein
Meteorite | Level 14

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;

Kurt_Bremser
Super User

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;

 

Ronein
Meteorite | Level 14

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

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
  • 3 replies
  • 714 views
  • 0 likes
  • 2 in conversation