Add sheets to existing excel file

huiyi213 — Sat, 09 Jan 2021 13:54:24 GMT

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;

Re: Add sheets to existing excel file

SASKiwi — Sat, 09 Jan 2021 22:21:44 GMT

Unfortunately not. The ODS approach requires that you build your Excel workbook from scratch each time. This is because ODS allows you to do output highly-formatted data that would be too difficult to interpret if you were adding sheets.

The solution is to use either the XLSX LIBNAME or PROC EXPORT using DBMS = XLSX. This will require you to build your worksheets as SAS datasets first before exporting them.

Re: Add sheets to existing excel file

huiyi213 — Wed, 13 Jan 2021 08:18:01 GMT

Oh okayy, thanks for your help anyway!

topic Add sheets to existing excel file in SAS Programming

Add sheets to existing excel file

Re: Add sheets to existing excel file

Re: Add sheets to existing excel file