Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook

Reply
Contributor
Posts: 34

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;

Super User
Super User
Posts: 9,376

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

Posted in reply to anitapamu2

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
  • 146 views
  • 0 likes
  • 2 in conversation