Hi, I would like to know it is possible to add sheets generated by ods excel onto an existing excel file which has the first sheet filled? My reports are being generated by proc statements, so i can't use proc export as I don't have output tables. Below is how my code looks like: libname pg1 "/folders/myfolders/EPG1V2/data"; %let outpath=/folders/myfolders/EPG1V2/data; title; ods excel file="&outpath/UAT_Blah_20201229.xlsx" style=excel options(embedded_titles='yes' sheet_name='Test 1' sheet_interval='none'); proc contents data=pg1.storm_final varnum; run; ods excel options(sheet_name='Test 2'); proc surveyselect data=pg1.storm_final out=table method=srs sampsize=10 seed=1234567 noprint; run; proc print data=table; run; ods excel options(sheet_name='Test 3' sheet_interval='none'); %macro prep_stats(v_base,v_group); title "Contents of &v_base"; proc contents data=&v_base out=storm_cols varnum noprint; run; data v_base2 noprint; set &v_base; run; title "Frequency of &v_base BY &v_group"; proc sql; select &v_group, count(*) as count from v_base2 group by &v_group; quit; title "Numeric Stats of &v_base"; proc means data=v_base2 n nmiss min max sum; var _numeric_; run; title "Date Stats of &v_base"; proc sql noprint; select NAME into :datevars separated by ' ' /*NAME contain column/variable names*/ from storm_cols where format = 'DATE'; quit; proc tabulate data=v_base2; var &datevars; table /*ROW*/ &datevars, /*COLUMN*/ (n nmiss (min max)*f=yymmdd10.); run; title "Number of Character Columns"; proc sql; create table tmp_char as select * from storm_cols where format="$CHAR"; /*Get total count of character columns*/ select count(*) into: Ncols from tmp_char; /*Get list of character column names*/ select NAME into :NAME1-:NAME%Left(&Ncols) from tmp_char; quit; %do i=1 %to &Ncols; proc sql noprint; select count(distinct &&NAME&i) into :d_cnt from v_base2; quit; %if &d_cnt<=100 %then /* if no of records less than 100 then run do */ %do; title "Unique Count of &&NAME&i"; /*List of counts for each element in each character column*/ proc sql; select &&NAME&i, count(*) as cnt from v_base2 group by &&NAME&i order by &&NAME&i; quit; %end; %end; %mend; %prep_stats(pg1.storm_final,StartDate); ods excel close;
... View more
