topic Re: Export Multiple tables into one XLSX sheet in SAS Programming

Export Multiple tables into one XLSX sheet

Ronein — Mon, 09 Aug 2021 14:09:08 GMT

Hello

I want to export multiple summary tables into one XLSX (EXCEL) file into one Sheet.

I want that the tables will be below each other with a few rows distance between them.

I also want that the columns titles will be with grey background.

IT might happend that need to adjust the columns width in order that we can see it well in Excel and in order that it will look nice.

What is the recommended way to do it please and please show the code , thanks

Title 'Table1: One Dimensional Table'
proc tabulate data=sashelp.baseball;
   class Team;
   tables Team;
quit;


Title 'Table2: One Dimensional Table'
proc tabulate data=sashelp.baseball;
   class Team;
   var nHome;
   tables Team, nHome;
quit;


Title 'Table3: One Dimensional Table'
proc tabulate data=sashelp.baseball;
   class Team League;
   var nHome;
   tables League, Team, nHome;
quit;


Title 'Table4: Three Dimensional Table';
 proc tabulate data=sashelp.baseball;
   class Team League;
   var nHome;
   tables League, Team, nHome;
quit;

TITLE ' Listing of Top 5 Rows of SASHELP.SHOES';
proc sql outobs=5;
 select * from sashelp.shoes;
 quit;

Title ' Summary Sales by Region';
proc means data=sashelp.shoes sum;
 class region;
 var sales;
 run;


data weight2;                      
input IDnumber $ Week1 Week16; 
WeightLoss2=Week1-Week16;     
datalines;       
2477 195  163
2431 220  198
2456 173  155
2412 135  116
;                            

proc print data=weight2;      
run;  


data investment;   
   begin='01JAN1990'd;
   end='31DEC2009'd;
   do year=year(begin) to year(end);   
      Capital+2000 + .07*(Capital+2000);
      output;    
   end;
   put 'The number of DATA step iterations is '_n_; 
run;


proc print data=investment;    
format Capital dollar12.2;  
run;

Re: Export Multiple tables into one XLSX sheet

Tom — Mon, 09 Aug 2021 14:26:56 GMT

Just check the documentation on ODS EXCEL destination.

Make sure to include semi-colons to end your TITLE statements.

ods excel file='c:\downloads\one_sheet.xlsx'
  options (embedded_titles='ON' sheet_interval='NONE' sheet_name='REPORT')
; 
Title 'Table1: One Dimensional Table';
proc tabulate data=sashelp.baseball;
   class Team;
   tables Team;
quit;


Title 'Table2: One Dimensional Table';
proc tabulate data=sashelp.baseball;
   class Team;
   var nHome;
   tables Team, nHome;
quit;

ods excel close;

Re: Export Multiple tables into one XLSX sheet

ballardw — Mon, 09 Aug 2021 17:09:13 GMT

The documentation for the TITLE statement shows the options for setting color height font background color text angles and such.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsglobal/p10gcmrmf83iaxn1ilrx4pra969n.htm