Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Reply
Contributor
Posts: 32

Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

HELLO , CAN ANYONE HELP ME WITH THIS MACRO

%macro sasxpt;

%let path=C:\Users\anita.pamu\Desktop\test2;

libname maxis "&path.";

proc sql;

select count(distinct memname) into:co from dictionary.columns where

libname="MAXIS";

select distinct memname into:dd1-:dd%left(&co) from dictionary.columns

where libname="MAXIS";

quit;

  %do i=1 %to &co;

  %let dd=dd;

proc export data=maxis.&&&dd.&i..

outfile="C:\Users\anita.pamu\Desktop\test2\excel.xlsx"

dbms=xlsx replace;

sheet="&&&dd.&i..";

quit;

  %end;

%mend;

options mprint mlogic symbolgen;

%sasxpt;

Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Its likely that your use of replace in the export is causing your problems as this creates a new file each time.

Why not do something like this which prints each dataset to the excelxp tasget on named sheets?:

data _null_;

     set sashelp.vtable (where=(libname="MAXIS")) end=last;

     if _n_=1 then call execute('ods tagsets.excelxp file="c:\abc.xls";');

     call execute('ods tagsets.excelxp options=(sheet_name="'||strip(memname)||'"); proc print data=maxis.'||strip(memname)||'; run;');

     if last then call execute('ods tagsets.excelxp close;');

run;

Ask a Question
Discussion stats
  • 1 reply
  • 119 views
  • 0 likes
  • 2 in conversation