Loading...
Searching...
No Matches
getcolvals.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Retrieves column info to enable population of dropdowns
4 @details An optional filterquery may be provided, if so then it is validated
5 and then used to filter the subsequent results.
6
7 <h4> Service Inputs </h4>
8
9 <h5> IWANT </h5>
10
11 The STARTROW and ROWS variables are used to fetch additional values beyond
12 the initial default (4000).
13
14 |libds:$19.|col:$9.|STARTROW:8.|ROWS:8.|
15 |---|---|---|---|
16 |DC258467.MPE_X_TEST|SOME_TIME|4001|1000
17
18 <h5> FILTERQUERY </h5>
19 |GROUP_LOGIC:$3|SUBGROUP_LOGIC:$3|SUBGROUP_ID:8.|VARIABLE_NM:$32|OPERATOR_NM:$10|RAW_VALUE:$32767|
20 |---|---|---|---|---|---|
21 |AND|AND|1|SOME_BESTNUM|>|1|
22 |AND|AND|1|SOME_TIME|=|77333|
23
24 <h4> Service Outputs </h4>
25 <h5> VALS </h5>
26 The type of this column actually depends on the underlying column type, so it can change
27 |FORMATTED|UNFORMATTED|
28 |---|---|
29 |$44.00|44|
30
31 <h5> META </h5>
32 |COLUMN:$32.|SASFORMAT:$32.|STARTROW:8.|ROWS:8.|
33 |---|---|---|---|
34 |COL_NAME|DOLLAR8.2|4001|1000
35
36 <h4> SAS Macros </h4>
37 @li mf_existds.sas
38 @li mf_getvalue.sas
39 @li mf_verifymacvars.sas
40 @li dc_assignlib.sas
41 @li mf_getvarformat.sas
42 @li mp_abort.sas
43 @li mp_cntlout.sas
44 @li mp_filtercheck.sas
45 @li mp_filtergenerate.sas
46
47 @version 9.2
48 @author 4GL Apps Ltd.
49 @copyright 4GL Apps Ltd. This code may only be used within Data Controller
50 and may not be re-distributed or re-sold without the express permission of
51 4GL Apps Ltd.
52
53**/
54%mpeinit()
55
56/* input table may or may not exist */
57data work.initvars;
58 length GROUP_LOGIC $3 SUBGROUP_LOGIC $3 SUBGROUP_ID 8 VARIABLE_NM $32
59 OPERATOR_NM $10 RAW_VALUE $32767;
60 call missing(of _all_);
61 stop;
62data work.filterquery;
63 set %sysfunc(ifc(
64 %mf_existds(work.filterquery)=1
65 ,work.filterquery
66 ,work.initvars
67 ));
68run;
69
70/* print data for debugging */
71data _null_;
72 set work.iwant;
73 put (_all_)(=);
74run;
75data _null_;
76 set work.filterquery;
77 put (_all_)(=);
78run;
79
80%let libds=%mf_getvalue(work.iwant,libds);
81%let col2=%mf_getvalue(work.iwant,col);
82%let is_fmt=0;
83%let startrow=1;
84%let rows=4000;
85
86%put &=libds;
87%put &=col2;
88
89%mp_abort(iftrue= (%mf_verifymacvars(libds col2)=0)
90 ,mac=&_program..sas
91 ,msg=%str(Missing inputs from iwant. Libds=&libds col=&col2 )
92)
93
94%dc_assignlib(WRITE,%scan(&libds,1,.))
95
96data _null_;
97 call missing(startrow,rows);
98 set work.iwant;
99 /* check if the request is for a format catalog */
100 call symputx('orig_libds',libds);
101 is_fmt=0;
102 if substr(cats(reverse(libds)),1,3)=:'CF-' then do;
103 libds=scan(libds,1,'-');
104 putlog "Format Catalog Captured";
105 call symputx('libds','work.fmtextract');
106 is_fmt=1;
107 end;
108 call symputx('is_fmt',is_fmt);
109 call symputx('startrow',coalesce(startrow,&startrow));
110 call symputx('rows',coalesce(rows,&rows));
111 putlog (_all_)(=);
112run;
113
114%mp_cntlout(
115 iftrue=(&is_fmt=1)
116 ,libcat=&orig_libds
117 ,fmtlist=0
118 ,cntlout=work.fmtextract
119)
120
121
122/**
123 * Validate the filter query
124 */
125%mp_filtercheck(work.filterquery,targetds=&libds,abort=YES)
126
127/**
128 * Prepare the query
129 */
130%mp_filtergenerate(work.filterquery,outref=myfilter)
131
132/* cannot %inc in a sql where clause, only data step, so - use a view */
133data work.vw_vals/view=work.vw_vals;
134 set &libds;
135 where %inc myfilter;;
136run;
137
138proc sql;
139create view work.vw_vals_sorted as
140 select distinct
141 put(&col2,%mf_getVarFormat(&libds,&col2,force=1)) as formatted,
142 &col2 as unformatted
143 from work.vw_vals;
144
145/* restrict num of output values */
146data work.vals;
147 set work.vw_vals_sorted;
148 if _n_ ge &startrow;
149 x+1;
150 if x>&rows then stop;
151 drop x;
152run;
153
154data vals;
155 /* ensure empty value if table is empty, for dropdowns */
156 if nobs=0 then output;
157 set vals nobs=nobs;
158 format unformatted ;
159 output;
160run;
161
162proc sql noprint;
163select count(*) into: nobs from work.vw_vals_sorted;
164data meta;
165 column="&col2";
166 sasformat="%mf_getVarFormat(&libds,&col2)";
167 startrow=&startrow;
168 rows=&rows;
169 nobs=&nobs;
170run;
171
172%mp_abort(iftrue= (&syscc ne 0)
173 ,mac=&_program..sas
174 ,msg=%str(syscc=&syscc)
175)
176
177%webout(OPEN)
178%webout(OBJ,vals,missing=STRING,showmeta=YES)
179%webout(OBJ,meta)
180%webout(CLOSE)
181
182
183%mpeterm()