Organizations use an increasing amount of reports. Countless hours are spent manipulating spreadsheets one after another, into and out of MS Access databases and finally emailing it to a select audience of people.
The following tries to present a simple and integrated approach to report generation and presentation. This involves the use of Macros, for repeatability and flexibility.You can copy and paste the code directly to your SAS editor and make relevant changes.
Steps in preparing a report
- Import data from data sources (spreadsheets, databases mostly .Here assuming BASE SAS ,only csv files are considered)
- Manipulating data for analysis (in the case below the only data manipulation done is removal of duplicates. This section can be changed as needed by user for
- Frequency counts using Proc Freq.
- Mean, maximum and minimum counts using Proc Means.
- Customized reports using Proc Report Procedure.)
- Presenting and formatting data
- Output data in the form of a report /spreadsheet.
- Emailing internal customers of the report.
———————————————————————————————————
Using BASE SAS, the following can be coded as:
/*PROGRAM TO AUTOMATE REPORTING */
/*DECLARING THE PATH OF THE INPUT/OUTPUT FOLDER */
%let pathfile = ‘X:\Auto\AUG06-AUTOS\’ ;
/*CREATING LIBRARY NAME */
libname auto &pathfile;
run;
/*TO CONSERVE SPACE*/
options compress=yes;
/*TO MAKE LOG READABLE */
options macrogen symbolgen;
/* STARTING MACRO FOR REPEATABLE EXECUTION OF REPORT*/
%macro impmth(mth,num,emailid);
/*MANIPULATING VARIABLES TO REDUCE CHANGES */
data _null_ ;
call symput(‘filepath’,”‘”||&pathfile||&mth||’.csv’||”‘” );
call symput(‘unqpath’,”‘”||&pathfile||”unq”||&mth||’.csv’||”‘” );
call symput(‘unqxls’,”‘”||&pathfile||”unq”||&mth||’.xls’||”‘” );
run;
/*IMPORT*/
/*IMPORTING DATA FROM CSV FILES STORED IN FOLDER DECLARED ABOVE*/
PROC IMPORT OUT= auto.&num
DATAFILE= &filepath
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
quit;
/*CREATING VARIABLE FOR DISTINGUISHING INPUT*/
data auto.#
set auto.#
&num =1;
run;
———————————————————————–
/*DATA MANIPULATION HERE REMOVING DUPLICATES*/
proc sort data=auto.&num nodupkey;
by REFAGREEMENTID;
run;
———————————————————————–
/*EXPORT*/
/*EXPORTING DATA TO A CSV FILE*/
PROC EXPORT DATA= AUTO.&num
OUTFILE= &unqpath
DBMS=CSV REPLACE;
RUN;
/*EXPORTING DATA TO A XLS FILE*/
ODS HTML FILE=&unqxls;
proc print data=auto.#
run;
ODS HTML CLOSE;
/*EMAILING THE RESULTS*/
filename outbox email &emailid;
data _null_ ;
file outbox
to=(&emailid)/* Overrides value in filename statement */
cc=(&emailid)
subject=’File from’
attach=(&unqxls)
;
put ‘Hi,’;
put ‘This is an integrated approach to automating reports’;
put ‘It works great!’;
run;
%mend;
/*CALLING THE MACRO*/
/*HERE ‘Ahmedabad’ IS THE INPUT FILE WHILE Ahd IS THE OUTPUT FILE*/
/*HERE ‘aohri@hotmail.com’ IS THE EMAIL ID TO BE MAILED THE REPORT*/
%impmth(‘Ahmedabad’,Ahd,’aohri@hotmail.com’);
Note- This was written on version 8 of SAS, so let me know any issues.
Ajay
DECISION STATS 