SUGI 28 Coders' Corner

Paper 101-28

Report? Make It Easy – An Example of Creating Dynamic Reports into

Excel

Liping Huang
Visiting Nurse Service of New York

Abstract The purpose of creating a user-friendly interactive

window application is to allow anyone who executes the
The ability to generate flexible reports in Excel is in great program to have flexibility to select criteria or a time
demand. This paper will use a real world example from a frame without editing codes. The %WINDOW REPORT
health care agency to illustrate an automated, flexible, shown bellow will bring a SAS AF window (see
easily managed process for intermediate and innovative Appendix I) and store user-defined criteria as global
SAS programmers to create updateable and individualized macro variables, which are used as conditional criteria
reports that include descriptive and statistical results. The while the program performs the rest of tasks.
/*Assign Global Macro Variables*/
paper will demonstrate the following capabilities by using
Base SAS version 8: (1) Using %WINDOW statement to %LET begdate = ;
create an interface allowing end users having flexibility to %LET enddate = ;
%LET region = ;
define a time frame and to select specific criteria. (2) %LET user = ;
Using macro %INPUTVAR to perform various analyses %LET password = ;
by simply adding or dropping variables listed in %let
statements. (3) Illustrate a procedure that adopts the %WINDOW REPORT
IROW= 4 ICOLUMN= 5 ROWS= 25 COLUMNS= 75
features from SAS Output Delivery System (ODS) to COLOR=RED GROUP= hdrftr
examine the variance between two samples and select a #2 @20 "Welcome to Outcome Reporting
proper probability, and subsequently export the System" COLOR=white
statistically significant results to an Excel template. #29 @5 "Press [ENTER]" COLOR=white
GROUP= criteria
#5 @5 "Please enter your user name"
Introduction COLOR=white +3 user 9 ATTR=UNDERLINE
AUTOSKIP=YES COLOR=white
#7 @5 "Please enter your user password"
In today’s health care industry, the ability to integrate COLOR=white +3 password 9
comprehensive information and generate flexible reports ATTR=UNDERLINE AUTOSKIP=YES COLOR=white
is in great demand. An easily understandable and #10 @5 "Please enter the report beginning
date(DDMONYYYY)" COLOR=white +3
updateable report plays an important role in assisting begdate 9 ATTR=UNDERLINE AUTOSKIP=YES
health care professionals to improve quality of care and in COLOR=white
supporting management to make better decisions. #12 @5 "Please enter the report ending
date(DDMONYYYY)" COLOR=white +3
enddate 9 ATTR=UNDERLINE AUTOSKIP=YES
Background COLOR=white blink
#17 @5 "Please enter a specific region from
The example used for this paper is from a large urban the following (B, K, M, Q, S, N): "
COLOR=white / #20 @5 region 40
health care agency. In order to provide higher quality of ATTR=UNDERLINE COLOR=white ;
services, the agency would like to have a quarterly report
but showing monthly information on adverse events %MACRO REPORT;
occurred during care. In addition, if a report were for a %DISPLAY report.hdrftr NOINPUT BLANK BELL ;
%DISPLAY report.criteria ;
specific region, the agency also would like to know if %MEND REPORT;
there is a statistical difference between a reporting region %REPORT;
and the rest of the agency.
%let borough= %bquote(')%upcase(&region)
Process Illustration %bquote(');
%let beg= %bquote(')%upcase(&begdate)
%bquote(');
Section I. Creating an Interactive Window Application %let end= %bquote(')%upcase(&enddate)
%bquote(');

1
SUGI 28 Coders' Corner

data _null_; CHISQ test and T-Test. It organizes results as a SAS data
call symput('cnt', put('if region = ' ||
"&borough", $20.));
set to be output to a pre-defined Excel template.
run;
%put &cnt &borough &beg &end; /*list variables for running frequencies or
means*/
As one can see from the log, if B is entered in the field of
%let advfreq=adv001_1 adv001_2 adv001_3
the region, the value of &cnt showed as: if borough = adv001_4 adv002_1 adv003_1
“B”. adv004_1 adv004_2 adv005_1
adv006_1 adv006_2 adv006_3
adv007_1;
Section II. Creating data sets %let varmean=los age total_visit;

The data is from the agency Oracle database. Using SQL As mentioned before, simply add and drop variables in
Pass-Through facility and the global macro variables the above statements, one will be able to meet needs from
generated from %WINDOW REPORT, a data set that the management. In addition, the following process shows
matches user-selected criteria is dynamically retrived how the program refers to the variables listed above and
from the Oracle database, named “data_all”. perform analyses by calling macros %DOFREQ,
Furthermore, a flag indicating if a patient was from a %DOMEAN, %DOCHISQ, and %DOTTEST, and
report region is also added. Data sets for a reporting organizes results into a desired order.
region only and non-reporting regions, named “pt_region”
and “pt_other” were created, respectively. /*create data sets in order to convert listed variables to macro
variables*/
proc sql;
connect to oracle (user=&user data advfreq;
password=&password path=rcprod1); input &advfreq;
create table datain as cards;
select * from connection to oracle run;
(select case_num, borough,
discharge_date, adv_item from data varmean;
rc_owner.case_facts input &varmean;
where discharge_date between &beg and cards;
&end); run;
disconnect from oracle;
quit; The following %INPUTVAR converts variables into
data data_all pt_region pt_other; macro variables and calls other macros to perform various
set datain; analyses, such as frequency, mean, CHISQ test and T-
&cnt then report=1; Test, and then it organizes results as a SAS data set to be
else report=0; output. The parameter &datain asks the data set for
if report>=0 then output data_all ;
if report=1 then output pt_region; analysis. It could be either data for the reporting region,
if report=0 then output pt_other; comparison regions, or all. The parameter &varin refers to
run; the group of variables listed in the above %let statements,
and the parameter &in is used to distinguish types of
Section III. Running Analyses Based on Selected analyses. It ranges from 0 to 3 that represents performing
Variables frequency, mean, t-test, and Chisq tests, respectively.
As we stated before, the purpose of this report is to
%macro INPUTVAR(datain, varin, in);
analyze adverse events that occurred during care in order
/*convert a user defined variable list from
to help the agency monitoring the quality of patient care a %let statement to macro variables*/
and making plans for improvement. Therefore, the most proc transpose data=&varin
reporting items involving this report is related to various out=var2;
run;
adverse events. In other words, in addition to overall proc sql noprint;
patient information, the report will present the frequencies select count(*) into :count
of adverse events within a region or across regions. Since from var2;
the strategies of the agency could vary along with the quit;
time, the emphasis of individual adverse categories may data _null_;
vary as well. Therefore, how to minimize the efforts on call symput('newcount',
modifications is the concern of programmers. By using compress('new') ||compress(&count));
the macro %INPUTVAR, as showed bellow, one will be run;
proc sql noprint;
able to perform various analyses by simply adding or select _name_ into :new1 thru
dropping variables listed in %let statements. The :&newcount from var2;
%INPUTVAR is a user-defined macro. It converts quit;
variables into macro variables and calls other macros to
perform various analyses, such as frequency, mean, %let count=&count;

2
SUGI 28 Coders' Corner

data var3(rename=(_name_=label)); get four data sets that represent final descriptive and
set var2;
obs=_n_;
statistics results for a reporting region and its comparison.
run;
proc sort data=var3; by label; run; Section VI. Loading Excel and output results into the
/*calling macros to run frequencies, template and save as a specific report
means, chisq and t-test*/
%if &in=0 %then %do;
%DOFREQ(&count); After results are calculated and organized as a SAS data
%end; set, it is time to output. There are a few different ways to
%if &in=1 %then %do; load Excel template. In the example, we use ‘X”
%DOMEAN(&count);
%end;
command to activate an Excel template and write outputs
%if &in=2 %then %do; into its “data” sheet. In addition, the codes below also
%DOTTEST(&count); write a regional title and a time frame into the data table,
%end; which will be dynamically reflected in a report title.
%if &in=3 %then %do;
%DOCHISQ(&count);
%end; /*Load an Excel template*/
%mend INPUTVAR; options noxwait noxsync;
x "c:\progra~1\micros~1\office\excel
c:\reports\template.xls";
Section III. Calculating Frequencies and Means
data _null_;
In a real world, information is usually presented as x = sleep(10);
run;
frequencies and means. The macros %DOFREQ and
%DOMEAN meet this purpose. Based on the variables /*count the total observations from the
listed from %let statements: &DOFREQ and &DOMEAN final data set in order to determine the
will give results as count, percentages, and means across number of rows needed to be filled in a
data sheet of the template*/
different months, and append results together for
outputting. Please note that the macro %EXIST proc sql;
determines whether a base data set exists while doing select count(*) into :count
appending. It deletes an existing base data set if it is the from finalout;
quit;
first run of a loop. /*output the final data set*/
filename data1 dde "excel|data!r3c1:
The detail codes for the %EXIST, %DOFREQ, AND r%eval(&count+3)c5" notab;
%DOMEAN are listed in Appendix II. data _null_;
set finalout;
file data1;
Section IV. Do CHISQ test and T-TEST if _n_ = 1 then put 'label' '09'x
'c_region' '09'x 'p_region' '09'x
'c_other' '09'x 'p_other' '09'x;
Although it is simple enough to obtain CHISQ results as a put label $25. '09'x
SAS data set through the features of the PROC FREQ, it c_region 8. '09'x
is a little tricky to have the results from a t-test organized p_region 8.3 '09'x
c_other 8. '09'x
as a SAS data set. The Base SAS version 8 has not had p_other 8.3 '09'x
the capability to create a SAS data set directly from the sign $8. '09'x;
procedure of PROC TTEST. Nevertheless, the macro run;
%DOTTEST adopts the features from SAS Output
/*Save the file as a specific report*/
Delivery System (ODS) and examine the variance filename cmds dde 'excel|system';
between two samples and select a proper probability, and data _null_;
subsequently export statistically significant results to the archive="c:\report_&region"||".xls";;
file cmds;
Excel template (see Appendix II for detail information of put '[QUIT()]';
%DOCHISQ and %DOTTEST. run;

Section V. Running Report Conclusion

As stated before, the macro %INPUTVAR converts the This paper has shown an automated process of handling
variables listed from %let statements into macro variables analytical results and outputting them to a pre-defined
and calls other macros %DOFREQ, %DOMEAN, Excel template. It is easily modifiable for a variety of
%DOCHISQ, and %DOTTEST. For instance, by projects. Without require extensive knowledge of SAS,
applying %INPUTVAR(pt_region, varfreq, one will be able to manipulate it with ease.
0),%INPUTVAR(pt_region, varmean,
1),%INPUTVAR(data_all, varmean, 2),and
%INPUTVAR(data_all, varmean, 3), one will

3
SUGI 28 Coders' Corner

References APPENDIX I

Vyverman, K. “Using Dynamic Data Exchange to Export SAS AF WINDOW: Generated from %REPORT
your SAS Data to MS Excel”. Proceedings of the twenty-
seventh Annual SAS Users Group International
Conference, paper 5, 2002.

Abdurazak, T. “Using SAS Macros to Create Automated

Excel Reports Containing Tables, Charts and Graphs”.
Proceedings of the twenty-seventh Annual SAS Users
Group International Conference, paper 126, 2002.

Mace, M. “%Window: You Can Talk to the Users, and

They Can Talk Back”. Proceedings of the twenty-seventh
Annual SAS Users Group International Conference,
paper 192, 2002.

Trademarks
APPENDIX II
SAS is a registered trademark of SAS Institute Inc. in the %macro EXIST(dsn);
USA and other countries. ® Indicates USA registration. %if %sysfunc(exist(&dsn)) %then %do;
proc datasets library=work;
Other brand name and product names are registered delete &dsn; quit;
%end;
trademarks or trademarks of their respective companies. %mend EXIST;

%macro DOFREQ(count);
%do i=1 %to &count;
Contact Information %let n=&i;
proc freq data=&datain noprint ;
Liping Huang tables month*&&new&n /list out=out;
Center for Home Care Policy and Research run; /*&&new&n was created from
%inputvar*/
Visiting Nurse Service of NY data out;
5 Penn Plaza, 11th Floor set out;
New York, NY 10001 if &&new&n >0;/*Only keep real
value*/
(212) 290 – 0520 proc transpose data=out
E-mail: liping.huang@vnsny.org (keep=count month)
out=outa(drop=_label_ _name_)
prefix=c_m;
id month;
proc transpose data=out(keep=percent
month)
out=outb(drop=_label_ _name_)
prefix=p_m;
id month;

/*merge count and percent together*/

data out&n;
length label $25.;
merge outa outb;
label="&&new&n";
run;
/*delete base data set if it is the
first run*/
%if &n=1 %then %do;
%exist(freq);
proc append data=out&n(rename=(&&new&n
=value))
base=freq force;
run;
%end;
%else %do;
proc append data=out&n(rename=(&&new&n
=value))
base=freq force;

4
SUGI 28 Coders' Corner

run; output out=out(keep=p_pchi)chisq;

%end; run;
%end;
/*organize data into a desired order*/ data out;
proc sort data=freq; by label; run; length label $20.;
data freq(drop=value); set out;
merge var3 freq; label="&&new&n";
/*var3 is a data set created from run;
%inputvar, with a desired order*/ /*delete the base file is it is the f
by label; run of appending*/
run; %if &n=1 %then %do;
proc sort data=freq; by obs; run; %exist(chisq);
%mend DOFREQ; proc append data=out base=chisq force;
run;
%end;
%macro DOMEAN(count);
%do i=1 %to &count; %else %do;
%let n=&i; proc append data=out
proc means data=&datain nway noprint ; base=chisq force;
class month; run;
var &&new&n; %end;
output out=out(keep=qtr count %end;
mean)n=count proc sort data=chisq;
mean=mean/autoname; by label;
run; run;
/*separated count and means*/
proc transpose data=out(keep=count /*making statistical judgement and organize
month) results to be output*/
out=outa(drop=_label_ _name_)
prefix=c_m; data chisq(keep=label sign);
id month; merge var3 chisq;
run; by label;
proc transpose data=out(keep=mean if p_pchi <= 0.001 then
month) sign='***';
out=outb(drop=_label_ _name_) else if 0.001 < p_pchi <
prefix=p_m; 0.01 then sign= '**';
id month; else if 0.01 < p_pchi
run; <= 0.05 then sign= '*';
/*combine outputs for count and mean together*/ run;
data out&n; proc sort data=chisq;
length label $25.; by obs; run;
merge outa outb; %mend DOCHISQ;
label="&&new&n";
run;
/*appending to base file*/ %macro DOTTEST(count);
%if &n=1 %then %do; %exist(mean); %do i=1 %to &count;
proc append data=mean&n %let n=&i;
base=mean force; /*using ODS features to store results
run; from t-test*/
%end; ods output ttests=out1(keep=variable
%else %do; variances tvalue probt);
proc append data=mean&n base=mean ods output Equality=out2
force; (keep=variable fvalue probf);
run; ods trace on;
%end; proc ttest data=&datain noprint;
%end; class report; var &&new&n;
proc sort data=mean; by label; run; run;
data mean; ods trace off;
merge var3 mean; /*using variance to measure group
by label; equalience*/
run; data out2(drop=variable)
proc sort data=mean; length label $20.;
by obs; run; merge out1 out2;
%mend DOMEAN; by variable;
if probf <=0.05 then do;
if variances='Unequal';
%macro DOCHISQ(count); /*output results with unequal
%do i=1 %to &count; variance*/
%let n=&i; end;
else do;
/*perform a CHISQ tests between reporting if variances='Equal';
region and comparison group*/ /*output results with equal
variance*/
proc freq data=&datain noprint; end;
tables report*&&new&n / chisq; label = " &&new&n"

5
SUGI 28 Coders' Corner

run;
%if &n=1 %then %do;
%exist(test);
proc append data=out2 base=test force;
run;
%end;
%else %do;
proc append data=out2 base=test force;
run;
%end;
%end;

/*create a final result set from ttest and

organized it into a desired order*/

proc sort data=test; by label;run;

proc sort data=var3; by label; run;

data ttest(keep=label sign);

merge var3 test;
by label;
if probt <= 0.001 then sign='***';
else if 0.001 <= probt < 0.01
then sign= '**';
else if 0.01 <=probt < 0.05
then sign= '*';
run;

proc sort data=ttest; by obs; run;

%mend DOTTEST;