GENERICITY

1
GENERICITY
New Metadata Concepts Applied to SAS Macro
Programming
2
Preface
What it is and what it is not This paper
presents part of my experiences using SAS Macro
technology over years with great pleasure and,
occasionally, with some success, at least for
customer satisfaction. Starting with an example
from an earlier presentation the question is
raised, how the positive correlation between
program flexibility and number of parameters can
be extinguished. By introducing the term
Reporting Specific Data Structures one idea is
described that allows to make use from metadata
already present in the runtime environment. This
paper is NOT about Programming using the SAS
Macro Facility.
3
Generic Programming
MACRO cr_tbl_4_fct GLOBAL fct src tbl
otb LOCAL i_frq n_frq l_frq i_ctl n_ctl
l_ctl IF SCAN(FCT.,1,'_') eq PRR THEN DO
/ start PRR main loop / LET n_frq
SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_') proc sql noprint select
name into p_key from dictionary.columns
where libname UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1 select
name , name into l_frq separated by ' '
, l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1) insert into header
values("PRR values calculated for
L2FRQ.") select name , name into l_ctl
separated by ' , ' , l2ctl separated by '
' from dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL.
1) insert into footer values("Stratification
performed by L2CTL.") insert into footer
select compbl(put(count(),8.)" lines
processed from UPCASE(DB_USR.).TBL.") from
SRC..TBL. insert into footer select
compbl("Frequency tables based on
"put(count(distinct P_KEY.),8.)" distinct
values from field P_KEY.") from
SRC..TBL. create table SRC..c_frq as select
distinct DO i_frq 1 TO N_FRQ. IF I_FRQ.
gt 1 THEN DO , END SCAN(L_FRQ.,I_FR
Q.,' ') END from SRC..TBL. where DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN DO
and END SCAN(L_FRQ.,I_FRQ.,' ') is
not null END LET n_c_frq SQLOBS. insert
into footer values("N_C_FRQ. combinations from
L2FRQ. processed") quit data _null_ set
SRC..c_frq DO i_frq 1 TO N_FRQ. call
symput("SCAN(L_FRQ.,I_FRQ.,'
')"'_'trim(left(put(_N_,4.))),trim(left(SCAN(
L_FRQ.,I_FRQ.,' ')))) END run DO i_c_frq
1 TO N_C_FRQ. / start FREQ loop / proc
sql create view WORK.tbl as select distinct
P_KEY. , DO i_frq 1 TO N_FRQ. LET
SCAN(L_FRQ.,I_FRQ.,' ') QUOTE(SCAN(L_FRQ
.,I_FRQ.,' ')_I_C_FRQ.) case when
sum( SCAN(L_FRQ.,I_FRQ.,' ')
"SCAN(L_FRQ.,I_FRQ.,' ')" ) gt 0 then
-1 else 0 end as SCAN(L_FRQ.,I_FRQ.
,' ')_rnk , END L_CTL. from SRC..TBL.
group by P_KEY. quit proc freq
noprint data WORK.tbl tables L2CTL. DO i_frq
1 TO N_FRQ. STR() SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmh(keep
_mhrrc1_ l_mhrrc1 u_mhrrc1) cmh1 run proc
freq noprint data WORK.tbl tables DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO STR() END SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmc(keep
l_mhrrc1 u_mhrrc1) cmh1 run proc freq
noprint data WORK.tbl tables DO i_frq 1
TO N_FRQ. IF I_FRQ. gt 1 THEN DO STR()
END SCAN(L_FRQ.,I_FRQ.,' ')_rnk END /
sparse out WORK.cnt(keep count) run proc
transpose data WORK.cnt out WORK.trp
run data WORK.res merge WORK.trp WORK.cmh
WORK.cmc(rename(l_mhrrc1l_mhrrcx
u_mhrrc1u_mhrrcx)) run proc sql
noprint create table WORK.out as select DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO , END "SCAN(L_FRQ.,I_FRQ.,'
')" as SCAN(L_FRQ.,I_FRQ.,' ') END
, col1 as a label "cell A" , col3 as b
label "cell B" , col2 as c label "cell
C" , col4 as d label "cell D" ,
(a/c)((cd)/(ab)) as cmh_crude label "PRR
crude" , l_mhrrcx as cmhlcrude label "PRR
crude lower CL" , u_mhrrcx as cmhucrude label
"PRR crude upper CL" , _mhrrc1_ as cmh_strat
label "PRR stratified" , l_mhrrc1 as
cmhlstrat label "PRR stratified lower CL" ,
u_mhrrc1 as cmhustrat label "PRR stratified
upper CL" from WORK.res IF I_C_FRQ. 1
THEN DO drop table SRC..TBL.SCAN(FCT.,1,_)
END create table SRC..TBL.SCAN(FCT.,1,_)
as IF I_C_FRQ. gt 1 THEN DO select from
SRC..TBL.SCAN(FCT.,1,_) union END select
from out quit END / end FREQ loop
/ proc sql create table SRC..TBL.SCAN(FCT.,
1,_) as IF SUBSTR(RST.,2,1) ne C THEN
DO select from SRC..TBL.SCAN(FCT.,1,_)
as tbl IF SUBSTR(RST.,1,1) ne R THEN DO
right END join SRC..TBL. as fct on DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO and END fct.SCAN(L_FRQ.,I_FRQ
.,' ') tbl.SCAN(L_FRQ.,I_FRQ.,'
') END END ELSE DO select from
SRC..TBL.SCAN(FCT.,1,_) END quit LET
otb TBL. LET tbl TBL.SCAN(FCT.,1,_) EN
D / end PRR main loop / MEND cr_tbl_4_fct
Concept

Metadata
4
Never forget
A problem well stated is a problem half
solved Charles Kettering (1876-1958) Founder
of the General Motors Research Corporation
5
Np f(F)
Number of Parameters
Flexibility
6
Intro

• When producing reports
• You might end up with
• One appropriately sized program per report
• One somehow macroized program per report type
• One Macro System for reporting domains

7
Start
For example
8
This Quest
9
Macro System
generated by a single macro system
10
System Architecture
User Modules Generate datasets carrying subtables
controlled by user-supplied parms
Core Modules Perform input transformation,
calculations and output transformation
Service Modules Provide frequently requested
tasks in a standard format with limited parameter
set
Info Modules Provide information about datasets
and variables for correct processing
11
Program Controlling
Required already a few parameters
12
full parameter set user modules
TWO_BOBO() Build super row (block) from
boolean selections nested in boolean
selection dsn input dataset name row,
row2 categorial variable name, 2list of
nested_vartrue_value rev Y/N (output decodes
of ROW in reverse order) use, use2 select
decode from ROW, 2decode from ROW used as
nesting context weight Y/N (multiply percentages
for ROW and ROW2) col categorial variable
name used for columns total T/I/B/O/N/TC/IC/BC/O
C/NC head, head2 Y/N (block header, 2nested
variable) indent, indinc n (number of indent
columns and increment for nested
variable) num n (sequence number of
output) stat Y/N (column with statistics
names) space 1/2/3 (blank line before or after
output and between nesting levels) struct,
struct2 name of reference dataset used for full
decode structure, 2nested variable condense var
value (non-distinct variable and true value for
ROW) misslin2 Y/N (force missing line for
nested variable)
13
user parameters - common
DSN Name of input dataset or view. This may be
any valid SAS dataset name (one-level or
two-level) not accompanied by dataset options or
other SAS syntax components. COL Name of
variable used to construct columns. The variable
is checked for number of levels and an
appropriate number of columns are generated. ROW
Name of variable to construct rows, superrows,
and subtables. Modules capable of processing more
than one variable accept a list here. HEAD
Optionally specify N to suppress output of the
header line for the row variable generated from
their label. In categorial processing the header
is an additional 1st line whereas in continuous
processing the header text is written left-hand
to the 1st stats line output. Default is
Y. STAT Optionally select Y to generate an
output column which contains the names of
statistics generated. Default is N. INDENT
Optionally select a positive integer to indent
the rows generated as one block. Default is
0. SPACE Optionally select spacing mode for
one-level subtables 0no blank lines 11st
output line is blank 2last output line is
blank. Default is 2. For two-level subtables
2insert additional blank line between upper and
lower level output 3only last output line is a
blank line. Default is 3. NUM Assigns a unique
number to the output generated. Only one digit is
allowed here.
14
Not amused

• This appears quite complicated
• Isnt there another way
• To have limited source code
• With a high level of flexibility
• And not to drown in parameter flood
• Lets have a closer look

15
Talk to me

• When running macro programs
• You may influence results on several levels
• Parameter passing (feed)
• Controlling (feed and prevent)
• Communication (feed, prevent and search)

16
Search

• Make your Macro a curious Communicator
• Implement a search mechanism that
• makes it follow a set of rules
• provides or
• generates knowledge on metadata in reach
• is fault-tolerant

17
Search

• Of course, this should be a Macro because you
  want to
• do it once
• do it generic
• use it forever

18
Metadata

• Metadata are all around
• Simple variable lists -NUMERIC-
• Libref dictionary tables, columns, etc.
• User defined repositories of any kind

19
Report Specific Data Structures
Lets focus on one One of the frequently
neglected or simply overseen information bits
from dictionary.columns is VARNUM. This may
result from historical reasons, since the SAS
dataset structure was learned as more or less
fixed. Reordering a datasets variables was not
supported very well by the SAS System and hence,
not used. Since concepts emerge from programming
habits
20
Report Specific Data Structures
SQL Views Today, since 1990, it is very easy to
reorder the virtual physical sequence of
variables in a dataset. The SAS System treats the
properties of an SQL view equally to those of an
old-fashioned somewhat clumsy SAS
Dataset. This is good News!
21
Report Specific Data Structures
Lets try a small example
22
data testdsnlabelaVariable A in SAS
DatasetbVariable B in SAS Dataseta22
b190runproc sqlcreate view testsql
asselect b as a label"Variable B from SAS
Dataset" , a as b label"Variable A from SAS
Dataset" from testdsnselect memname ,
name , label , varnum from
dictionary.columns where memname like
'TEST___'quit
Report Specific Data Structures
23
Report Specific Data Structures
Member Name Column Name Column Label Column Numberin Table
TESTDSN a Variable A in SAS Dataset 1
TESTDSN b Variable B in SAS Dataset 2
TESTSQL a Variable B from SAS Dataset 1
TESTSQL b Variable A from SAS Dataset 2
24
Report Specific Data Structures

• Access Layer
• Obviously, utilizing the select clause in an SQL
  view adds a high amount of information to the
  data structure. This is not surprising, in case
  you
• are a physicist
• can count from zero to 1023 using ten fingers

25
Information Gain by Ordering
Source http//courses.geoplanet.ca/ice3m/image/bi
nary_hand_1-7.gif
26
Report Specific Data Structures
Now lets have a look a real life
27
Report Specific Data Structures
28
Report Specific Data Structures
29
Resulting Output
30
Howto
MACRO cr_tbl_4_fct GLOBAL fct src tbl
otb LOCAL i_frq n_frq l_frq i_ctl n_ctl
l_ctl IF SCAN(FCT.,1,'_') eq PRR THEN DO
/ start PRR main loop / LET n_frq
SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_') proc sql noprint select
name into p_key from dictionary.columns
where libname UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1 select
name , name into l_frq separated by ' '
, l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1) insert into header
values("PRR values calculated for
L2FRQ.") select name , name into l_ctl
separated by ' , ' , l2ctl separated by '
' from dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL.
1) insert into footer values("Stratification
performed by L2CTL.") insert into footer
select compbl(put(count(),8.)" lines
processed from UPCASE(DB_USR.).TBL.") from
SRC..TBL. insert into footer select
compbl("Frequency tables based on
"put(count(distinct P_KEY.),8.)" distinct
values from field P_KEY.") from
SRC..TBL. create table SRC..c_frq as select
distinct DO i_frq 1 TO N_FRQ. IF I_FRQ.
gt 1 THEN DO , END SCAN(L_FRQ.,I_FR
Q.,' ') END from SRC..TBL. where DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN DO
and END SCAN(L_FRQ.,I_FRQ.,' ') is
not null END LET n_c_frq SQLOBS. insert
into footer values("N_C_FRQ. combinations from
L2FRQ. processed") quit data _null_ set
SRC..c_frq DO i_frq 1 TO N_FRQ. call
symput("SCAN(L_FRQ.,I_FRQ.,'
')"'_'trim(left(put(_N_,4.))),trim(left(SCAN(
L_FRQ.,I_FRQ.,' ')))) END run DO i_c_frq
1 TO N_C_FRQ. / start FREQ loop / proc
sql create view WORK.tbl as select distinct
P_KEY. , DO i_frq 1 TO N_FRQ. LET
SCAN(L_FRQ.,I_FRQ.,' ') QUOTE(SCAN(L_FRQ
.,I_FRQ.,' ')_I_C_FRQ.) case when
sum( SCAN(L_FRQ.,I_FRQ.,' ')
"SCAN(L_FRQ.,I_FRQ.,' ')" ) gt 0 then
-1 else 0 end as SCAN(L_FRQ.,I_FRQ.
,' ')_rnk , END L_CTL. from SRC..TBL.
group by P_KEY. quit proc freq
noprint data WORK.tbl tables L2CTL. DO i_frq
1 TO N_FRQ. STR() SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmh(keep
_mhrrc1_ l_mhrrc1 u_mhrrc1) cmh1 run proc
freq noprint data WORK.tbl tables DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO STR() END SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmc(keep
l_mhrrc1 u_mhrrc1) cmh1 run proc freq
noprint data WORK.tbl tables DO i_frq 1
TO N_FRQ. IF I_FRQ. gt 1 THEN DO STR()
END SCAN(L_FRQ.,I_FRQ.,' ')_rnk END /
sparse out WORK.cnt(keep count) run proc
transpose data WORK.cnt out WORK.trp
run data WORK.res merge WORK.trp WORK.cmh
WORK.cmc(rename(l_mhrrc1l_mhrrcx
u_mhrrc1u_mhrrcx)) run proc sql
noprint create table WORK.out as select DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO , END "SCAN(L_FRQ.,I_FRQ.,'
')" as SCAN(L_FRQ.,I_FRQ.,' ') END
, col1 as a label "cell A" , col3 as b
label "cell B" , col2 as c label "cell
C" , col4 as d label "cell D" ,
(a/c)((cd)/(ab)) as cmh_crude label "PRR
crude" , l_mhrrcx as cmhlcrude label "PRR
crude lower CL" , u_mhrrcx as cmhucrude label
"PRR crude upper CL" , _mhrrc1_ as cmh_strat
label "PRR stratified" , l_mhrrc1 as
cmhlstrat label "PRR stratified lower CL" ,
u_mhrrc1 as cmhustrat label "PRR stratified
upper CL" from WORK.res IF I_C_FRQ. 1
THEN DO drop table SRC..TBL.SCAN(FCT.,1,_)
END create table SRC..TBL.SCAN(FCT.,1,_)
as IF I_C_FRQ. gt 1 THEN DO select from
SRC..TBL.SCAN(FCT.,1,_) union END select
from out quit END / end FREQ loop
/ proc sql create table SRC..TBL.SCAN(FCT.,
1,_) as IF SUBSTR(RST.,2,1) ne C THEN
DO select from SRC..TBL.SCAN(FCT.,1,_)
as tbl IF SUBSTR(RST.,1,1) ne R THEN DO
right END join SRC..TBL. as fct on DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO and END fct.SCAN(L_FRQ.,I_FRQ
.,' ') tbl.SCAN(L_FRQ.,I_FRQ.,'
') END END ELSE DO select from
SRC..TBL.SCAN(FCT.,1,_) END quit LET
otb TBL. LET tbl TBL.SCAN(FCT.,1,_) EN
D / end PRR main loop / MEND cr_tbl_4_fct
LET n_frq SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_')
31
Howto
MACRO cr_tbl_4_fct GLOBAL fct src tbl
otb LOCAL i_frq n_frq l_frq i_ctl n_ctl
l_ctl IF SCAN(FCT.,1,'_') eq PRR THEN DO
/ start PRR main loop / LET n_frq
SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_') proc sql noprint select
name into p_key from dictionary.columns
where libname UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1 select
name , name into l_frq separated by ' '
, l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1) insert into header
values("PRR values calculated for
L2FRQ.") select name , name into l_ctl
separated by ' , ' , l2ctl separated by '
' from dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL.
1) insert into footer values("Stratification
performed by L2CTL.") insert into footer
select compbl(put(count(),8.)" lines
processed from UPCASE(DB_USR.).TBL.") from
SRC..TBL. insert into footer select
compbl("Frequency tables based on
"put(count(distinct P_KEY.),8.)" distinct
values from field P_KEY.") from
SRC..TBL. create table SRC..c_frq as select
distinct DO i_frq 1 TO N_FRQ. IF I_FRQ.
gt 1 THEN DO , END SCAN(L_FRQ.,I_FR
Q.,' ') END from SRC..TBL. where DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN DO
and END SCAN(L_FRQ.,I_FRQ.,' ') is
not null END LET n_c_frq SQLOBS. insert
into footer values("N_C_FRQ. combinations from
L2FRQ. processed") quit data _null_ set
SRC..c_frq DO i_frq 1 TO N_FRQ. call
symput("SCAN(L_FRQ.,I_FRQ.,'
')"'_'trim(left(put(_N_,4.))),trim(left(SCAN(
L_FRQ.,I_FRQ.,' ')))) END run DO i_c_frq
1 TO N_C_FRQ. / start FREQ loop / proc
sql create view WORK.tbl as select distinct
P_KEY. , DO i_frq 1 TO N_FRQ. LET
SCAN(L_FRQ.,I_FRQ.,' ') QUOTE(SCAN(L_FRQ
.,I_FRQ.,' ')_I_C_FRQ.) case when
sum( SCAN(L_FRQ.,I_FRQ.,' ')
"SCAN(L_FRQ.,I_FRQ.,' ')" ) gt 0 then
-1 else 0 end as SCAN(L_FRQ.,I_FRQ.
,' ')_rnk , END L_CTL. from SRC..TBL.
group by P_KEY. quit proc freq
noprint data WORK.tbl tables L2CTL. DO i_frq
1 TO N_FRQ. STR() SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmh(keep
_mhrrc1_ l_mhrrc1 u_mhrrc1) cmh1 run proc
freq noprint data WORK.tbl tables DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO STR() END SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmc(keep
l_mhrrc1 u_mhrrc1) cmh1 run proc freq
noprint data WORK.tbl tables DO i_frq 1
TO N_FRQ. IF I_FRQ. gt 1 THEN DO STR()
END SCAN(L_FRQ.,I_FRQ.,' ')_rnk END /
sparse out WORK.cnt(keep count) run proc
transpose data WORK.cnt out WORK.trp
run data WORK.res merge WORK.trp WORK.cmh
WORK.cmc(rename(l_mhrrc1l_mhrrcx
u_mhrrc1u_mhrrcx)) run proc sql
noprint create table WORK.out as select DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO , END "SCAN(L_FRQ.,I_FRQ.,'
')" as SCAN(L_FRQ.,I_FRQ.,' ') END
, col1 as a label "cell A" , col3 as b
label "cell B" , col2 as c label "cell
C" , col4 as d label "cell D" ,
(a/c)((cd)/(ab)) as cmh_crude label "PRR
crude" , l_mhrrcx as cmhlcrude label "PRR
crude lower CL" , u_mhrrcx as cmhucrude label
"PRR crude upper CL" , _mhrrc1_ as cmh_strat
label "PRR stratified" , l_mhrrc1 as
cmhlstrat label "PRR stratified lower CL" ,
u_mhrrc1 as cmhustrat label "PRR stratified
upper CL" from WORK.res IF I_C_FRQ. 1
THEN DO drop table SRC..TBL.SCAN(FCT.,1,_)
END create table SRC..TBL.SCAN(FCT.,1,_)
as IF I_C_FRQ. gt 1 THEN DO select from
SRC..TBL.SCAN(FCT.,1,_) union END select
from out quit END / end FREQ loop
/ proc sql create table SRC..TBL.SCAN(FCT.,
1,_) as IF SUBSTR(RST.,2,1) ne C THEN
DO select from SRC..TBL.SCAN(FCT.,1,_)
as tbl IF SUBSTR(RST.,1,1) ne R THEN DO
right END join SRC..TBL. as fct on DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO and END fct.SCAN(L_FRQ.,I_FRQ
.,' ') tbl.SCAN(L_FRQ.,I_FRQ.,'
') END END ELSE DO select from
SRC..TBL.SCAN(FCT.,1,_) END quit LET
otb TBL. LET tbl TBL.SCAN(FCT.,1,_) EN
D / end PRR main loop / MEND cr_tbl_4_fct
select name into p_key from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1
32
Howto
MACRO cr_tbl_4_fct GLOBAL fct src tbl
otb LOCAL i_frq n_frq l_frq i_ctl n_ctl
l_ctl IF SCAN(FCT.,1,'_') eq PRR THEN DO
/ start PRR main loop / LET n_frq
SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_') proc sql noprint select
name into p_key from dictionary.columns
where libname UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1 select
name , name into l_frq separated by ' '
, l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1) insert into header
values("PRR values calculated for
L2FRQ.") select name , name into l_ctl
separated by ' , ' , l2ctl separated by '
' from dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL.
1) insert into footer values("Stratification
performed by L2CTL.") insert into footer
select compbl(put(count(),8.)" lines
processed from UPCASE(DB_USR.).TBL.") from
SRC..TBL. insert into footer select
compbl("Frequency tables based on
"put(count(distinct P_KEY.),8.)" distinct
values from field P_KEY.") from
SRC..TBL. create table SRC..c_frq as select
distinct DO i_frq 1 TO N_FRQ. IF I_FRQ.
gt 1 THEN DO , END SCAN(L_FRQ.,I_FR
Q.,' ') END from SRC..TBL. where DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN DO
and END SCAN(L_FRQ.,I_FRQ.,' ') is
not null END LET n_c_frq SQLOBS. insert
into footer values("N_C_FRQ. combinations from
L2FRQ. processed") quit data _null_ set
SRC..c_frq DO i_frq 1 TO N_FRQ. call
symput("SCAN(L_FRQ.,I_FRQ.,'
')"'_'trim(left(put(_N_,4.))),trim(left(SCAN(
L_FRQ.,I_FRQ.,' ')))) END run DO i_c_frq
1 TO N_C_FRQ. / start FREQ loop / proc
sql create view WORK.tbl as select distinct
P_KEY. , DO i_frq 1 TO N_FRQ. LET
SCAN(L_FRQ.,I_FRQ.,' ') QUOTE(SCAN(L_FRQ
.,I_FRQ.,' ')_I_C_FRQ.) case when
sum( SCAN(L_FRQ.,I_FRQ.,' ')
"SCAN(L_FRQ.,I_FRQ.,' ')" ) gt 0 then
-1 else 0 end as SCAN(L_FRQ.,I_FRQ.
,' ')_rnk , END L_CTL. from SRC..TBL.
group by P_KEY. quit proc freq
noprint data WORK.tbl tables L2CTL. DO i_frq
1 TO N_FRQ. STR() SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmh(keep
_mhrrc1_ l_mhrrc1 u_mhrrc1) cmh1 run proc
freq noprint data WORK.tbl tables DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO STR() END SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmc(keep
l_mhrrc1 u_mhrrc1) cmh1 run proc freq
noprint data WORK.tbl tables DO i_frq 1
TO N_FRQ. IF I_FRQ. gt 1 THEN DO STR()
END SCAN(L_FRQ.,I_FRQ.,' ')_rnk END /
sparse out WORK.cnt(keep count) run proc
transpose data WORK.cnt out WORK.trp
run data WORK.res merge WORK.trp WORK.cmh
WORK.cmc(rename(l_mhrrc1l_mhrrcx
u_mhrrc1u_mhrrcx)) run proc sql
noprint create table WORK.out as select DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO , END "SCAN(L_FRQ.,I_FRQ.,'
')" as SCAN(L_FRQ.,I_FRQ.,' ') END
, col1 as a label "cell A" , col3 as b
label "cell B" , col2 as c label "cell
C" , col4 as d label "cell D" ,
(a/c)((cd)/(ab)) as cmh_crude label "PRR
crude" , l_mhrrcx as cmhlcrude label "PRR
crude lower CL" , u_mhrrcx as cmhucrude label
"PRR crude upper CL" , _mhrrc1_ as cmh_strat
label "PRR stratified" , l_mhrrc1 as
cmhlstrat label "PRR stratified lower CL" ,
u_mhrrc1 as cmhustrat label "PRR stratified
upper CL" from WORK.res IF I_C_FRQ. 1
THEN DO drop table SRC..TBL.SCAN(FCT.,1,_)
END create table SRC..TBL.SCAN(FCT.,1,_)
as IF I_C_FRQ. gt 1 THEN DO select from
SRC..TBL.SCAN(FCT.,1,_) union END select
from out quit END / end FREQ loop
/ proc sql create table SRC..TBL.SCAN(FCT.,
1,_) as IF SUBSTR(RST.,2,1) ne C THEN
DO select from SRC..TBL.SCAN(FCT.,1,_)
as tbl IF SUBSTR(RST.,1,1) ne R THEN DO
right END join SRC..TBL. as fct on DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO and END fct.SCAN(L_FRQ.,I_FRQ
.,' ') tbl.SCAN(L_FRQ.,I_FRQ.,'
') END END ELSE DO select from
SRC..TBL.SCAN(FCT.,1,_) END quit LET
otb TBL. LET tbl TBL.SCAN(FCT.,1,_) EN
D / end PRR main loop / MEND cr_tbl_4_fct
select name , name into l_frq separated
by ' ' , l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1)
33
Howto
MACRO cr_tbl_4_fct GLOBAL fct src tbl
otb LOCAL i_frq n_frq l_frq i_ctl n_ctl
l_ctl IF SCAN(FCT.,1,'_') eq PRR THEN DO
/ start PRR main loop / LET n_frq
SCAN(FCT.,2,'_') LET n_ctl
SCAN(FCT.,3,'_') proc sql noprint select
name into p_key from dictionary.columns
where libname UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum eq 1 select
name , name into l_frq separated by ' '
, l2frq separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt 1 and varnum
le EVAL(N_FRQ. 1) insert into header
values("PRR values calculated for
L2FRQ.") select name , name into l_ctl
separated by ' , ' , l2ctl separated by '
' from dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL.
1) insert into footer values("Stratification
performed by L2CTL.") insert into footer
select compbl(put(count(),8.)" lines
processed from UPCASE(DB_USR.).TBL.") from
SRC..TBL. insert into footer select
compbl("Frequency tables based on
"put(count(distinct P_KEY.),8.)" distinct
values from field P_KEY.") from
SRC..TBL. create table SRC..c_frq as select
distinct DO i_frq 1 TO N_FRQ. IF I_FRQ.
gt 1 THEN DO , END SCAN(L_FRQ.,I_FR
Q.,' ') END from SRC..TBL. where DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN DO
and END SCAN(L_FRQ.,I_FRQ.,' ') is
not null END LET n_c_frq SQLOBS. insert
into footer values("N_C_FRQ. combinations from
L2FRQ. processed") quit data _null_ set
SRC..c_frq DO i_frq 1 TO N_FRQ. call
symput("SCAN(L_FRQ.,I_FRQ.,'
')"'_'trim(left(put(_N_,4.))),trim(left(SCAN(
L_FRQ.,I_FRQ.,' ')))) END run DO i_c_frq
1 TO N_C_FRQ. / start FREQ loop / proc
sql create view WORK.tbl as select distinct
P_KEY. , DO i_frq 1 TO N_FRQ. LET
SCAN(L_FRQ.,I_FRQ.,' ') QUOTE(SCAN(L_FRQ
.,I_FRQ.,' ')_I_C_FRQ.) case when
sum( SCAN(L_FRQ.,I_FRQ.,' ')
"SCAN(L_FRQ.,I_FRQ.,' ')" ) gt 0 then
-1 else 0 end as SCAN(L_FRQ.,I_FRQ.
,' ')_rnk , END L_CTL. from SRC..TBL.
group by P_KEY. quit proc freq
noprint data WORK.tbl tables L2CTL. DO i_frq
1 TO N_FRQ. STR() SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmh(keep
_mhrrc1_ l_mhrrc1 u_mhrrc1) cmh1 run proc
freq noprint data WORK.tbl tables DO i_frq
1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO STR() END SCAN(L_FRQ.,I_FRQ.,'
')_rnk END / cmh1 output out WORK.cmc(keep
l_mhrrc1 u_mhrrc1) cmh1 run proc freq
noprint data WORK.tbl tables DO i_frq 1
TO N_FRQ. IF I_FRQ. gt 1 THEN DO STR()
END SCAN(L_FRQ.,I_FRQ.,' ')_rnk END /
sparse out WORK.cnt(keep count) run proc
transpose data WORK.cnt out WORK.trp
run data WORK.res merge WORK.trp WORK.cmh
WORK.cmc(rename(l_mhrrc1l_mhrrcx
u_mhrrc1u_mhrrcx)) run proc sql
noprint create table WORK.out as select DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO , END "SCAN(L_FRQ.,I_FRQ.,'
')" as SCAN(L_FRQ.,I_FRQ.,' ') END
, col1 as a label "cell A" , col3 as b
label "cell B" , col2 as c label "cell
C" , col4 as d label "cell D" ,
(a/c)((cd)/(ab)) as cmh_crude label "PRR
crude" , l_mhrrcx as cmhlcrude label "PRR
crude lower CL" , u_mhrrcx as cmhucrude label
"PRR crude upper CL" , _mhrrc1_ as cmh_strat
label "PRR stratified" , l_mhrrc1 as
cmhlstrat label "PRR stratified lower CL" ,
u_mhrrc1 as cmhustrat label "PRR stratified
upper CL" from WORK.res IF I_C_FRQ. 1
THEN DO drop table SRC..TBL.SCAN(FCT.,1,_)
END create table SRC..TBL.SCAN(FCT.,1,_)
as IF I_C_FRQ. gt 1 THEN DO select from
SRC..TBL.SCAN(FCT.,1,_) union END select
from out quit END / end FREQ loop
/ proc sql create table SRC..TBL.SCAN(FCT.,
1,_) as IF SUBSTR(RST.,2,1) ne C THEN
DO select from SRC..TBL.SCAN(FCT.,1,_)
as tbl IF SUBSTR(RST.,1,1) ne R THEN DO
right END join SRC..TBL. as fct on DO
i_frq 1 TO N_FRQ. IF I_FRQ. gt 1 THEN
DO and END fct.SCAN(L_FRQ.,I_FRQ
.,' ') tbl.SCAN(L_FRQ.,I_FRQ.,'
') END END ELSE DO select from
SRC..TBL.SCAN(FCT.,1,_) END quit LET
otb TBL. LET tbl TBL.SCAN(FCT.,1,_) EN
D / end PRR main loop / MEND cr_tbl_4_fct
select name , name into l_ctl separated
by ' , ' , l2ctl separated by ' ' from
dictionary.columns where libname
UPCASE("SRC.") and memname
UPCASE("TBL.") and varnum gt EVAL(N_FRQ.
1) and varnum le EVAL(N_FRQ. N_CTL. 1)
34
Questions welcome
35
THANK YOU