genericity
DESCRIPTION
GENERICITY. New Metadata Concepts Applied to SAS Macro Programming. 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. - PowerPoint PPT PresentationTRANSCRIPT
120 Apr 2023
GENERICITY
New Metadata Concepts Applied to SAS Macro Programming
Wolf-Dieter Batz: New Metadata Concepts
220 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
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.
320 Apr 2023
Generic Programming
Wolf-Dieter Batz: New Metadata Concepts
Concept
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* end PRR main loop */
%MEND cr_tbl_4_fct;
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* end PRR main loop */
%MEND cr_tbl_4_fct;
Metadata
+
420 Apr 2023
“A problem well stated is a problem half solved“
Charles Kettering (1876-1958) Founder of the General Motors Research Corporation
Never forget
Wolf-Dieter Batz: New Metadata Concepts
520 Apr 2023
Np = f(F)
Wolf-Dieter Batz: New Metadata Concepts
Flexibility
Num
ber
of P
aram
eter
s
620 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Intro
When producing reportsYou might end up with
- One appropriately sized program per report
- One somehow macroized program per report type
- One Macro System for reporting domains
720 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Start
For example:
820 Apr 2023
Total subjects treated 2821 ( 100) 2823 ( 100) Start of study medication within 24 hrs after randomization Yes 2751 (97.5) 2775 (98.3) No 66 ( 2.3) 47 ( 1.7) Surgery delayed 59 ( 2.1) 41 ( 1.5) Other 13 ( 0.5) 13 ( 0.5) Missing - ( - ) - ( - ) Missing 4 ( 0.1) 1 ( 0.0) Study medication only from kit allocated Yes 2821 ( 100) 2823 ( 100) Missing - ( - ) - ( - ) Number of subjects who did not receive full loading 26 ( 0.9) 12 ( 0.4) dose Reason: Adverse event 3 ( 0.1) 3 ( 0.1) Technical failure / dosing error 17 ( 0.6) 8 ( 0.3) Missing 6 ( 0.2) 1 ( 0.0) At least 75% of loading dose administered Yes 2808 (99.5) 2819 (99.9) No 8 ( 0.3) 4 ( 0.1) Missing 5 ( 0.2) - ( - ) Number of subjects with maintenance dose 86 ( 3.0) 101 ( 3.6) interruptions longer than 1 hour Number of maintenance dose interruptions 1 79 ( 2.8) 98 ( 3.5) 2 4 ( 0.1) 4 ( 0.1)
This Quest
Wolf-Dieter Batz: New Metadata Concepts
920 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Macro System
generated by a single macro system
1020 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
System Architecture
Core ModulesPerform input
transformation, calculations and
output transformation
User ModulesGenerate datasets carrying subtables controlled by user-
supplied parms
Service ModulesProvide frequently
requested tasks in a standard format with limited parameter set
Info ModulesProvide information about datasets and variables for correct
processing
1120 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Program Controlling
Required already a few parameters
1220 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
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, 2=list of nested_var#true_value
rev Y/N (output decodes of &ROW in reverse order)
use, use2 select decode from &ROW, 2=decode 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/OC/NC
head, head2 Y/N (block header, 2=nested 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, 2=nested variable
condense var#value (non-distinct variable and true value for &ROW)
misslin2 Y/N (force missing line for nested variable)
1320 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
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: 0=no blank lines; 1=1st output line is blank; 2=last output line is blank. Default is “2”. For two-level subtables: 2=insert additional blank line between upper and lower level output; 3=only 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.
1420 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Not amused
This appears quite complicatedIsn’t there another way
- To have limited source code
- With a high level of flexibility
- And not to drown in parameter flood
Let’s have a closer look…
1520 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Talk to me
When running macro programsYou may influence results on several levels
- Parameter passing (“feed”)
- Controlling (“feed and prevent”)
- Communication (“feed, prevent and search”)
1620 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
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
1720 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Search
Of course, this should be a Macro because you want to
- do it once
- do it generic
- use it forever
1820 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Metadata
Metadata are all around- Simple variable lists: “-NUMERIC-”
- Libref “dictionary”: tables, columns, etc.
- User defined repositories of any kind
1920 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
Let’s focus on oneOne 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 dataset’s variables was not supported very well by the SAS System and hence, not used.
Since concepts emerge from programming habits…
2020 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
SQL ViewsToday, 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!
2120 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Let’s try a small example:
Report Specific Data Structures
2220 Apr 2023
data testdsn;labela=Variable A in SAS Datasetb=Variable B in SAS Dataset;a=22; b=190;run;proc sql;create view testsql asselect b as a label="Variable B from SAS Dataset" , a as b label="Variable A from SAS Dataset" from testdsn;select memname , name , label
, varnum from dictionary.columns where memname like 'TEST___';quit;
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
2320 Apr 2023
Member Name Column Name Column LabelColumn Numberin Table
TESTDSN aVariable A in SAS Dataset
1
TESTDSN bVariable B in SAS Dataset
2
TESTSQL aVariable B from SAS Dataset
1
TESTSQL bVariable A from SAS Dataset
2
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
2420 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
Access LayerObviously, 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
2520 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Information Gain by Ordering
Source: http://courses.geoplanet.ca/ice3m/image/binary_hand_1-7.gif
2620 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Now let’s have a look a real life:
Report Specific Data Structures
2720 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
2820 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Report Specific Data Structures
2920 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Resulting Output
3020 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* end PRR main loop */
%MEND cr_tbl_4_fct;
%LET n_frq = %SCAN(&FCT.,2,'_');%LET n_ctl = %SCAN(&FCT.,3,'_');
Howto
3120 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* 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;
Howto
3220 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* 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);
Howto
3320 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
%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 asselect distinct%DO i_frq = 1 %TO &N_FRQ.;%IF &I_FRQ. gt 1 %THEN %DO;,%END; %SCAN(&L_FRQ.,&I_FRQ.,' ')%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 asselect 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.,' ')" ) > 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_mhrrc1=l_mhrrcx u_mhrrc1=u_mhrrcx)); run;
proc sql noprint;create table WORK.out asselect %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)*((c+d)/(a+b)) 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. > 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,_);%END; /* 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);
Howto
3420 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
Questions welcome
3520 Apr 2023
Wolf-Dieter Batz: New Metadata Concepts
THANK YOU