Hello,
I have a program that takes7 validation reports and exports them into an xlsx file on my sas unix folder. I didn't include it in here but the but the SAS program then attaches the excel file in an email and sends out to a group. Just a general question - how would I change the program to instead format each tab using SAS ODS to make it look cleaner and then export to a file on my windows directory?
Here is the code I currently use
%LET VALRPRTS = TMSHT_VAL_1,TMSHT_VAL_2,TMSHT_VAL_3,TMSHT_VAL_4,TMSHT_VAL_5,TMSHT_VAL_6,TMSHT_VAL_7;
%MACRO VALIDATION_REPORTS;
%DO I=1 %TO %SYSFUNC(COUNTW(%BQUOTE(&VALRPRTS),%STR(,)));
%LET NXTREPRT = %SCAN(%BQUOTE(&VALRPRTS),&I);
/* EXPORT THE RESULTS TO THE SAS GLOBAL DIRECTORY FOLDER */
PROC EXPORT
DATA=WORK.&NXTREPRT
OUTFILE="/sas/data/Law_Grp_Risk_Rv/shared/23-Risk_review/2_Libraries/RRGGLBL/RRG_VAL_FNL.XLSX"
DBMS=XLSX REPLACE;
SHEET="&NXTREPRT";
RUN;
%END;
%MEND VALIDATION_REPORTS;
1. Add an ODS EXCEL statement to create the Excel file
2. In the loop use an ODS EXCEL statement to specify the formatting options you want and the specific sheet name.
3. Use PROC PRINT/REPORT to export your data
That should be all.
If your datasets are all the same format with variables and such, you could stack them and use BY group processing so each tab is created automatically and no loops are needed but I have no idea what your reports are supposed to look like or what your data looks like.
The process is sketched out below, I have not tested this code and it's likely buggy but should give you the idea to get started.
%LET VALRPRTS = TMSHT_VAL_1,TMSHT_VAL_2,TMSHT_VAL_3,TMSHT_VAL_4,TMSHT_VAL_5,TMSHT_VAL_6,TMSHT_VAL_7; %MACRO VALIDATION_REPORTS; ods excel file='path to xlsx file' options(list options that will apply to all sheets here) style=meadow; *You can also specify your style here; %DO I=1 %TO %SYSFUNC(COUNTW(%BQUOTE(&VALRPRTS),%STR(,))); %LET NXTREPRT = %SCAN(%BQUOTE(&VALRPRTS),&I); /* EXPORT THE RESULTS TO THE SAS GLOBAL DIRECTORY FOLDER */ ods excel options(sheet_interval='now' sheet_name = "&nxtreprt."); *add options; PROC print data=&nxtreprt.; ..... run; %END; ods excel close; %MEND VALIDATION_REPORTS;
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 25. 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.