Outputting multiple datasets from SAS into one excel output with multiple spreadsheets

Question

Hello I have this program but needed to output pat1, dm1, ie1 etc into one excel output but with each dataset in a separate excel spreadsheet. Would ODS output be able to do this?. In the below example I'm using a preprogrammed macro but only works for one dataset and not for more than one. I would really appreciate any input on this as I'm working over my holidays here and would just like to get this over and done with

      *****************************************************************************
Date Created: 23-AUG-2020
Program: dsn2xlsmlti.sas
***************************************************************************
;
OPTIONS NOCENTER VALIDVARNAME=UPCASE;

%let pgm_path = %sysfunc(IFC(%symexist(pgm_path),%nrstr(&pgm_path),%sysfunc(dequote(&_sasprogramfile)))) ;
%put pgm_path=&pgm_path.;
%put pgm_path=&pgm_path;
%let curdir=&pgm_path/..;
%put curdir=&curdir.;

libname here "&pgm_path/../";

* Modify the following two lines to point to the source and target directories.;
%LET SOURC=&curdir./../../sas_data;
%LET TARGET=&curdir.;

* *** LMG2 27-JAN-2017 Assign default spreadsheet name.  Can be manually assigned below.;
%let studyno = %scan(&curdir.,7,/);
%LET SSNAME=&STUDYNO._&SYSDATE9.;
%put ssname=&ssname;
FILENAME LOGFILE "&curdir./DSN2EXCELXP.LOG" TERMSTR=CRLF;
FILENAME LSTFILE "&curdir./DSN2EXCELXP.LST" TERMSTR=CRLF;

PROC PRINTTO LOG=LOGFILE FILE=LSTFILE NEW;
RUN;

ODS RESULTS = OFF;

* Change curdir below, for SAS dataset not in current directory.;
LIBNAME SOURC "&SOURC.";

%include "/mnt/disk3/SAS/development/templates/stat/macros/cleaneg.sas";
%include "/mnt/disk3/SAS/development/templates/dm/macros/makedir.mac";

* Make target folder;
%makedir(&target);
LIBNAME TARGET "&TARGET.";




data pat1;
    set SOURC.pat;
    retain stno subjid PTENRL PTDROP;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid PTENRL PTDROP;
    rename usubjid= subjid;
run;



data visit1;
    set SOURC.visit;
    retain stno subjid visitn visdat vistnd;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol stno stname usubjid visitn visdat vistnd;
    rename usubjid= subjid;
run;

data dm1;
    set SOURC.dm;
    retain stno subjid BRTHDAT SEX ETHNIC RACE RACEOTH;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    raceoth=upcase(raceoth);
    keep protocol usubjid BRTHDAT SEX ETHNIC RACE RACEOTH;
    rename usubjid= subjid;
run;

data ie1;
    set SOURC.ie;
    retain stno subjid ieyn;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid ieyn;
    rename usubjid= subjid;
run;

data lab1;
    set SOURC.lab_tdl_cs;
    retain stno subjid visitn lbdat lbtim lbcat lbtestcd lborres lborresu lbornrlo lbornrhi  ABNRML lbcom ;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn lbdat lbtim lbcat lbtestcd lborres lborresu lbornrlo lbornrhi  ABNRML lbcom;
    rename usubjid= subjid;
run;

data mh1;
    set SOURC.mh;
    retain stno subjid mhyn category mhbody mhterm mhstdat mhongo mhendat ;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid mhyn category mhbody mhterm mhstdat mhongo mhendat;
    rename usubjid= subjid;
run;

data spiro1;
    set SOURC.spiro;
    retain stno subjid visitn visitno visitdt lbdat3 lbtim3 lbstyhr spiroyn prebronc fvc fev1 fevpred fevfvcr spirocom ;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt lbdat3 lbtim3 lbstyhr spiroyn prebronc fvc fev1 fevpred fevfvcr spirocom;
    rename usubjid= subjid;
run;

data feno1;
    set SOURC.feno;
    retain stno subjid visitn visitno visitdt fenotm studyhr fenoppb fenoacc;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt fenotim studyhr fenoppb fenoacc;
    rename usubjid= subjid;
run;

data tb1;
    set SOURC.tb;
    retain stno subjid visitn visitno visitdt tobyn product tobspfy tobnum tbdat tbedat;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt tobyn product tobspfy tobnum tbdat tbedat;
    rename usubjid= subjid;
run;

data bsc1;
    set SOURC.BSC;
    retain stno subjid visitn visitno visitdt bscyn pknd bssdyhr bscdat bsctim pkcomm;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt bscyn pknd bssdyhr bscdat bsctim pkcomm;
    rename usubjid= subjid;
run;

data aeyn1;
    set SOURC.aeyn;
    retain stno subjid visitn visitno visitdt aeyn aeterm;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt aeyn aeterm;
    rename usubjid= subjid;
run;

data cmyn;
    merge SOURC.cmyn;
    retain stno subjid visitn visitno visitdt cmyn cmtrt;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt cmyn cmtrt;
    rename usubjid= subjid;
run;

proc sort;
by subjid; 

data cm;
    merge SOURC.cm;
    retain stno subjid visitn visitno visitdt cmtrt;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt cmtrt;
    rename usubjid= subjid;
run;

proc sort;
by subjid;

data cmyn1;
merge cmyn cm;
by subjid;
run;

data bronc1;
    set SOURC.bronc;
    retain stno subjid visitn visitno visitdt broncyn BRCONDAT BRONCTIM BRONCBAL BRONTURB BRONVOLB BRONVBF BRONBTIM BRONBCOL BRONLCNT BRONVL BRONLVP BRONCNUM BRONCYTO BRONCTYN BRONNEUT
BRONEOS BRONMACR BRONLRAW BRONEPI;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid visitn visitno visitdt broncyn BRCONDAT BRONCTIM BRONCBAL BRONTURB BRONVOLB BRONVBF BRONBTIM BRONBCOL BRONLCNT BRONVL BRONLVP BRONCNUM BRONCYTO BRONCTYN BRONNEUT
BRONEOS BRONMACR BRONLRAW BRONEPI;
    rename usubjid= subjid;
run;

data acq1;
    set SOURC.acq;
    retain stno subjid acqyn acqdat acqscore;
    protocol= "0175";
    usubjid= "0175" || strip(stno)|| strip(subjid);
    keep protocol usubjid acqyn acqdat acqscore;
    rename usubjid= subjid;
run;

ods excel file='myfile.xlsx';
proc print data=pat1; run;
proc print data=visit1; run;
proc print data=dm1; run;
proc print data=lab1; run;
proc print data=mh1; run;
proc print data=spiro1; run;
proc print data=feno1; run;
proc print data=tb1; run;
proc print data=bsc1; run;
proc print data=aeyn1; run;
proc print data=cmyn1; run;
proc print data=bronc1; run;
proc print data=acq1; run;


ods excel close;


proc printto;
run;

FILENAME LOGFILE CLEAR ;
FILENAME LSTFILE CLEAR ;
LIBNAME SOURC CLEAR ;
LIBNAME TARGET CLEAR ;

TITLE1; FOOTNOTE1;

ODS RESULTS = ON ;

Answer 1

Just open the ODS EXCEL destination at the beginning. Dump each dataset using your preferred procedure (proc print, proc report,...). Then close it. There are options to control when a new sheet is started, but the default is for each new procedure to make a new sheet. There are also options to let you control the names of the sheets.

ods excel file='myfile.xlsx';
ods excel options(sheet_name="PAT1");
proc print data=pat1; run;
ods excel options(sheet_name="DM1");
proc print data=dm1; run;
...
ods excel close;