Excel ODS & Export from Unix to Windows

[ElvisK]

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;

[Reeza]

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;