topic Re: How to load tables one below the other in an excel spreadsheet dynamically in SAS? in ODS and Base Reporting

How to load tables one below the other in an excel spreadsheet dynamically in SAS?

Banana19 — Mon, 19 Sep 2022 20:07:16 GMT

Hi All, I'm looking for a solution where multiple tables are loaded one below the other dynamically.

Here is an Output example;

Attached is the code I used

ODS EXCEL FILE = "&LOCATION/ODS_TEST1.XLSX"; 
	ODS EXCEL OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						START_AT 			= "2,2"
						EMBEDDED_TITLES 	= "NO"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");

		PROC REPORT DATA=WORK.TEMP NOWD; 
			COLUMN ('TABLE1'(COLUMN_A COLUMN_B COLUMN_C));
			DEFINE COLUMN_A	/ "COLUMN_A";
			DEFINE COLUMN_B / "COLUMN_B";
			DEFINE COLUMN_C	/ "COLUMN_C";
		RUN; 

	ODS EXCEL OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						START_AT 			= "2,10"
						EMBEDDED_TITLES 	= "NO"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");

		PROC REPORT DATA=WORK.TEMP2 NOWD; 
			COLUMN ('TABLE2'(COLUMN_A COLUMN_B COLUMN_C));
			DEFINE COLUMN_A	/ "COLUMN_A";
			DEFINE COLUMN_B / "COLUMN_B";
			DEFINE COLUMN_C	/ "COLUMN_C";
		RUN;

ODS EXCEL CLOSE;

In the above code there are only 2 proc reports. As shown in O/P example, I have 4 different datasets and I only want to use the mentioned 2 proc report codes to generate 4 different tables one below the other dynamically without adding more proc reports.

It would be good if some one could help me with this issue and let me know if you need anything else.

Thanks,

Re: How to load tables one below the other in an excel spreadsheet dynamically in SAS?

SASKiwi — Mon, 19 Sep 2022 20:21:04 GMT

Using a macro is one way to reduce the code required.

%macro Make_Report (dataset = );

ODS EXCEL OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						START_AT 			= "2,10"
						EMBEDDED_TITLES 	= "NO"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");

		PROC REPORT DATA= &dataset. NOWD; 
			COLUMN ('TABLE2'(COLUMN_A COLUMN_B COLUMN_C));
			DEFINE COLUMN_A	/ "COLUMN_A";
			DEFINE COLUMN_B / "COLUMN_B";
			DEFINE COLUMN_C	/ "COLUMN_C";
		RUN;

%mend Make_Report;

%Make_Report (dataset = WORK.TEMP);
%Make_Report (dataset = WORK.TEMP2);
%Make_Report (dataset = WORK.TEMP3);
%Make_Report (dataset = WORK.TEMP4);

Re: How to load tables one below the other in an excel spreadsheet dynamically in SAS?

Reeza — Mon, 19 Sep 2022 21:24:25 GMT

Quick FYI - START_AT only works for the first table. Every reference afterwards is ignored and location is in reference to the first table.

That being said, this is a simple layout that can be accomplished within ODS EXCEL.

Re: How to load tables one below the other in an excel spreadsheet dynamically in SAS?

Reeza — Mon, 19 Sep 2022 23:01:11 GMT

You need EMBEDDED_TITLES=ON since you have them shown in the sheet.

Closer to what you want.

%macro report(n=);
 title "Report &n";
 proc print data=sashelp.class (obs=3) label noobs;
 var name age sex;
 run;
 
%mend;

ods excel file = '/home/fkhurshed/Demo1/demo.xlsx'  OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						EMBEDDED_TITLES 	= "ON"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");
						
%report(n=1);
%report(n=2);
%report(n=3);
%report(n=4);

ods excel close;

@Banana19 wrote:

Hi All, I'm looking for a solution where multiple tables are loaded one below the other dynamically.

Here is an Output example;

Attached is the code I used
ODS EXCEL FILE = "&LOCATION/ODS_TEST1.XLSX"; 
	ODS EXCEL OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						START_AT 			= "2,2"
						EMBEDDED_TITLES 	= "NO"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");

		PROC REPORT DATA=WORK.TEMP NOWD; 
			COLUMN ('TABLE1'(COLUMN_A COLUMN_B COLUMN_C));
			DEFINE COLUMN_A	/ "COLUMN_A";
			DEFINE COLUMN_B / "COLUMN_B";
			DEFINE COLUMN_C	/ "COLUMN_C";
		RUN; 

	ODS EXCEL OPTIONS(    
						SHEET_INTERVAL 		= "NONE"
						START_AT 			= "2,10"
						EMBEDDED_TITLES 	= "NO"
						SHEET_LABEL 		= "NA"
						SHEET_NAME 			= "NA"
						ABSOLUTE_ROW_HEIGHT = "15"
						ZOOM 				= "100");

		PROC REPORT DATA=WORK.TEMP2 NOWD; 
			COLUMN ('TABLE2'(COLUMN_A COLUMN_B COLUMN_C));
			DEFINE COLUMN_A	/ "COLUMN_A";
			DEFINE COLUMN_B / "COLUMN_B";
			DEFINE COLUMN_C	/ "COLUMN_C";
		RUN;

ODS EXCEL CLOSE;
In the above code there are only 2 proc reports. As shown in O/P example, I have 4 different datasets and I only want to use the mentioned 2 proc report codes to generate 4 different tables one below the other dynamically without adding more proc reports.

It would be good if some one could help me with this issue and let me know if you need anything else.

Thanks,

BD