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
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);
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: