BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cbt2119
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

View solution in original post

2 REPLIES 2
Cynthia_sas
SAS Super FREQ

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

cbt2119
Obsidian | Level 7
Thank you - this works well!

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 5098 views
  • 0 likes
  • 2 in conversation