Macros for SAS Application Developers
https://github.com/sasjs/core
mp_filterstore.test.2.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief Testing mp_filterstore macro with a format catalog
4 
5  <h4> SAS Macros </h4>
6  @li mp_assert.sas
7  @li mp_assertdsobs.sas
8  @li mp_assertscope.sas
9  @li mp_coretable.sas
10  @li mp_filterstore.sas
11 
12 **/
13 
14 libname permlib (work);
15 
16 %mp_coretable(LOCKTABLE,libds=permlib.locktable)
17 %mp_coretable(FILTER_SUMMARY,libds=permlib.filtsum)
18 %mp_coretable(FILTER_DETAIL,libds=permlib.filtdet)
19 %mp_coretable(MAXKEYTABLE,libds=permlib.maxkey)
20 
21 /* valid filter */
22 data work.inds;
23  infile datalines4 dsd;
24  input GROUP_LOGIC:$3. SUBGROUP_LOGIC:$3. SUBGROUP_ID:8. VARIABLE_NM:$32.
25  OPERATOR_NM:$12. RAW_VALUE:$4000.;
26 datalines4;
27 AND,AND,1,Start,>,"'2'"
28 AND,AND,1,Fmtname,=,"'MORDOR'"
29 OR,OR,2,Label,IN,"('Dragon1','Dragon2')"
30 OR,OR,2,End,=,"'6'"
31 OR,OR,2,Start,GE,"'10'"
32 ;;;;
33 run;
34 
35 /* make some formats */
36 PROC FORMAT library=permlib.testfmts;
37  picture MyMSdt other='%0Y-%0m-%0dT%0H:%0M:%0S' (datatype=datetime);
38 RUN;
39 data work.fmts;
40  length fmtname $32;
41  do fmtname='SMAUG','MORDOR','GOLLUM';
42  do start=1 to 10;
43  label= cats('Dragon ',start);
44  output;
45  end;
46  end;
47 run;
48 proc sort data=work.fmts nodupkey;
49  by fmtname start;
50 run;
51 proc format cntlin=work.fmts library=permlib.testfmts;
52 run;
53 proc format library=permlib.testfmts;
54  invalue indays (default=13) other=42;
55 run;
56 
57 
58 %mp_assertscope(SNAPSHOT)
59 %mp_filterstore(libds=permlib.testfmts-fc,
60  queryds=work.inds,
61  filter_summary=permlib.filtsum,
62  filter_detail=permlib.filtdet,
63  lock_table=permlib.locktable,
64  maxkeytable=permlib.maxkey,
65  outresult=work.result,
66  outquery=work.query,
67  mdebug=1
68 )
69 %mp_assertscope(COMPARE)
70 
71 %mp_assert(iftrue=(&syscc=0),
72  desc=Ensure macro runs without errors,
73  outds=work.test_results
74 )
75 /* ensure only one record created */
76 %mp_assertdsobs(permlib.filtsum,
77  desc=Initial query,
78  test=ATMOST 1,
79  outds=work.test_results
80 )
81 /* check RK is correct */
82 proc sql noprint;
83 select max(filter_rk) into: test1 from work.result;
84 %mp_assert(iftrue=(&test1=1),
85  desc=Ensure filter rk is correct,
86  outds=work.test_results
87 )
88 
89 /* Test 2 - load same table again and ensure we get the same RK */
90 %mp_filterstore(libds=permlib.testfmts-fc,
91  queryds=work.inds,
92  filter_summary=permlib.filtsum,
93  filter_detail=permlib.filtdet,
94  lock_table=permlib.locktable,
95  maxkeytable=permlib.maxkey,
96  outresult=work.result,
97  outquery=work.query,
98  mdebug=1
99 )
100 /* ensure only one record created */
101 %mp_assertdsobs(permlib.filtsum,
102  desc=Initial query - same obs,
103  test=ATMOST 1,
104  outds=work.test_results
105 )
106 /* check RK is correct */
107 proc sql noprint;
108 select max(filter_rk) into: test2 from work.result;
109 %mp_assert(iftrue=(&test2=1),
110  desc=Ensure filter rk is correct for second run,
111  outds=work.test_results
112 )