topic Exporting to an existing excel workbook with formats in ODS and Base Reporting

Exporting to an existing excel workbook with formats

cbt2119 — Tue, 15 Jan 2019 19:02:50 GMT

Dear SAS Community,

I need to generate a workbook that has two sheets:

1) List of ID's, dates, groups etc.

2) Summary data tables

I tried outputting the data tables first using ods excel and then using proc export to add the list to the existing workbook, but I lose all the formatting in my dataset when I try this. I can't seem to find a way to do both online. Does anyone know the best way to accomplish this?

I've attached my code below:

ods noproctitle;
ods excel file="Z:\folder\file.xlsx" options(sheet_name="Summary Data" sheet_interval="none");
title 'Group Totals';
proc freq data=work.dataset; tables log_group / missing nocum; format log_group groupf.; run;

title 'Sample Totals';
proc freq data=work.dataset; tables totalplasma / missing nocum; label totalplasma='Total Plasma'; run;

title 'Log Date Types';
proc freq data=work.dataset; tables visit / missing nocum; label 
title;
ods excel close;

proc datasets library = work nolist;
	modify dataset;
	format log_group groupf.;
quit;

PROC EXPORT DATA= WORK.dataset
            OUTFILE= "Z:\folder\file.xlsx" 
            DBMS=EXCEL REPLACE;
			PUTNAMES=YES;
     SHEET="List"; 
RUN;

Thanks,

Cara

Re: Exporting to an existing excel workbook with formats

Cynthia_sas — Tue, 15 Jan 2019 20:26:30 GMT

Hi:

This worked for me. Note that it does not use PROC EXPORT.

ods noproctitle;
ods excel file="c:\temp\cara_file.xlsx" style=htmlblue
    options(sheet_name="Summary Data" sheet_interval="none");
title 'Group Totals';
proc freq data=sashelp.shoes; 
tables region / missing nocum; 
run;
 
title 'Sample Totals';
proc freq data=sashelp.shoes; 
tables subsidiary / missing nocum; 
run;
 
title 'Log Date Types';
proc freq data=sashelp.shoes; 
tables product / missing nocum; 
run;
 
ods excel options(sheet_interval='table' sheet_name='List Data') style=minimal;
 
proc print  data=sashelp.shoes noobs;
format sales dollar14.2 returns dollar14. inventory dollar14.;
run;
 
ods excel close;

Instead of Export, I used PROC PRINT to list the data. That allowed me to do away with the PROC DATASETS step because a format can be assigned directly in the PROC PRINT step. (And, since Excel might not respect the SAS Format, this method works as well as having a permanent format in SAS.)

Your titles will not show inside the sheet unless you turn on the embedded_titles suboption. With my approach, the HTMLBLUE style is used for the 1st sheet and the minimal (or black and white) is used for the 2nd sheet.

Cynthia

Re: Exporting to an existing excel workbook with formats

cbt2119 — Tue, 15 Jan 2019 20:46:07 GMT

Thank you - this works well!