topic export to excel with special needs in ODS and Base Reporting

export to excel with special needs

Ronein — Tue, 10 Oct 2017 05:42:52 GMT

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;

Re: export to excel with special needs

Ronein — Tue, 10 Oct 2017 05:58:51 GMT

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;

Re: export to excel with special needs

Kurt_Bremser — Tue, 10 Oct 2017 06:41:18 GMT

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;

Re: export to excel with special needs

Ronein — Tue, 10 Oct 2017 10:31:42 GMT

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