Transcript Dynamic Generation of Data Steps

Dynamic Generation of Data Steps
on basis of Unique By-Group Permutations
David Rosenfeld
City of Toronto
Overview
•
•
•
•
•
•
TASS
the data
the problem
a solution
the macro
the output
the conclusion
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Data
data tass_sample;
input division $10. score;
label score='Test Score';
cards;
Division_A 1
Division_B 2
Division_B 8
Division_B 7
Division_B 6
Division_A 5
;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Problem
• need to export separate worksheets
for each division
• the task has to be repeated every
week
• don’t know which divisions will be in
the data file
• don’t want to hardcode (and have to
change) the program every week
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
A Solution *
Create a Macro to:
• dynamically produce the desired
datasets
• use subsetting criteria
• based on the values of a ‘by’
variable
derived from sample 26140
http://support.sas.com/kb/26/140.html
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Macro
%macro groups(dsn,byvar,export_to);
/* First sort the data in order of the */
/* by variable
*/
proc sort data=&dsn.;
by &byvar.;
run;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Macro
%macro
groups(dsn,byvar,export_to);
/* Then, create a macro variable, VARn, for
*/
/* each BY-Group and a counter of the number */
/*/*First
sort
the data
in order
of the */
of new
macro
variables
created.
*/
/*data
by variable
_null_;
procset
sort
data=&dsn.;
&dsn.
end=eof;
by&byvar.;
&byvar.;
by
run;if first.&byvar. then do;
*/
flag+1;
call symput('var'||put(flag,8. -L),&byvar.);
end;
if eof then call symput('tot',put(flag,8. -L));
run;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Macro
%macro groups(dsn,byvar,export_to);
/* Then, create a macro variable, VARn, for
*/
/* each
BY-Group
and a counter
of the
number
*/
/*
Loop
through
all
the
macro
variables
/*/*First
data
in order of the
*/
of new sort
macro the
variables
created.
*/
and */
/* create a separate file for each by variable */
/*data
by_null_;
variable
*/
i=1 %to &tot;
setdata
&dsn. %do
end=eof;
proc
sort
data=&dsn.;
by division; &&var&i
by
&byvar.;
if first.division then do;
run;flag+1; %end;;
callset
symput('var'||put(flag,8.
-L),division);
&dsn;
end;
i=1
%to &tot;
if eof%do
then call
symput('tot',put(flag,8.
-L));
run;
if &byvar="&&var&i" then output &&var&i;
%end;
run;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Macro
%macro groups(dsn,byvar,export_to);
/* Then, create a macro variable, VARn, for
*/
/*
BY-Group
andall
a
counter
ofthe
the
number and
*/
/* Loop
macro
variables
*/
/* each
First
sortthrough
the data
inthe
order
of
*/
/* of/*new
variables
created.
*/ */
create
/*macro
Loop
a separate
through
file
all
for the
eachmacro
by variable
variables
and */
/* by variable
*/
/* export
a sheet for each by variable
*/
proc
sort
data=&dsn.;
data _null_;
data
%do i=1 %to &tot;
by &byvar.;
set
&dsn. end=eof;
%do
j=1 %to &tot;
&&var&i
run;
by division;
%end;;
if first.division
then do;
PROC
EXPORT DATA= WORK.&&var&j
set
&dsn;
flag+1;
OUTFILE=
"&export_to."
%do
i=1 %to
&tot;
call symput('var'||put(flag,8.
-L),division);
end;if &byvar="&&var&i"
then output
&&var&i;
DBMS=EXCEL
REPLACE;
if %end;
eof then call symput('tot',put(flag,8. -L));
/* dbdsopts='dblabel=yes'; */
run;
run;
SHEET="&&var&j";
RUN;
%end;
%mend groups;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Macro
%macro groups(dsn,byvar,export_to);
/* Then, create a macro variable, VARn, for
*/
/*
BY-Group
andall
a
counter
ofthe
the
number and
*/
/* Loop
macro
variables
*/
/* each
First
sortthrough
the data
inthe
order
of
*/
/* of/*new
macro
variables
created.
*/ and
a through
separate
each*/
by variable
*/ */
/* Loop
allfile
theformacro
variables
/* by create
variable
/* export
a sheet for each by variable
*/
proc
sort
data=&dsn.;
data _null_;
data
%do i=1 %to &tot;
by &byvar.;
set
&dsn. end=eof;
&&var&i
j=1 %to &tot;
run;
by %do
division;
%end;;
PROC
DATA=
if first.division
then
/*
RunEXPORT
the do;
macro
*/ WORK.&&var&j
set
&dsn;
OUTFILE= "&export_to."
flag+1;
%do
i=1
%to &tot;
DBMS=EXCEL
REPLACE;
call symput('var'||put(flag,8.
-L),division);
%groups(work.tass_sample,
end;if &byvar="&&var&i"
then output*/&&var&i;
/* dbdsopts='dblabel=yes';
division,
if %end;
eof thenSHEET="&&var&j";
call symput('tot',put(flag,8. -L));
run;
c:\data_driven_demo.xls)
run; RUN;
%end;
%mend groups;
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
The Output
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
Conclusion
a useful method to:
• remove annoying drudgery
• avoid having to continually check data
• eliminate tedious and inefficient hard coding
• promote automated and easily maintained
sparse code
• have code that is flexible and reacts to data
transparently
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010
Questions?
TASS
Dynamic Generation of Data Steps on
basis of Unique By-Group Permutations
June 11, 2010