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 ;1 Answer
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;
