mpe_tables.dsn.sas
Go to the documentation of this file.
1 /**
2  @file
3  @brief fetch extended values for DSN
4  @details Fetches datasets in a library, and ALSO fetches a list of numeric
5  vars for each dataset for use in adjacent columns (such as VAR_PROCESSED,
6  TX_TO etc).
7 
8  Available macro variables:
9  @li MPELIB - The DC control library
10  @li LIBDS - The library.dataset being filtered
11  @li VARIABLE_NM - The column being filtered
12 
13 
14  <h4> Service Outputs </h4>
15  Output should be a single table called "work.dynamic_values" in the format
16  below. display_value should always be character, raw_value is unformatted
17  character/numeric.
18 
19  <h5>DYNAMIC_VALUES</h5>
20  The RAW_VALUE column may be charactor or numeric. If DISPLAY_INDEX is not
21  provided, it is added automatically.
22 
23  |DISPLAY_INDEX:best.|DISPLAY_VALUE:$|RAW_VALUE|
24  |---|---|---|
25  |1|$77.43|77.43|
26  |2|$88.43|88.43|
27 
28  <h5>DYNAMIC_EXTENDED_VALUES</h5>
29  This table is optional. If provided, it will map the DISPLAY_INDEX from the
30  DYNAMIC_VALUES table to additional column/value pairs, that will be used to
31  populate dropdowns for _other_ cells in the _same_ row.
32 
33  Should be used sparingly! The use of large tables here can slow down the
34  browser.
35 
36  The FORCED_VALUE column can be used to force an extended value to be selected
37  by default when a particular value is chosen.
38 
39  |DISPLAY_INDEX:best.|EXTRA_COL_NAME:$32.|DISPLAY_VALUE:$|DISPLAY_TYPE:$1.|RAW_VALUE_NUM|RAW_VALUE_CHAR:$5000|FORCED_VALUE|
40  |---|---|---|---|
41  |1|DISCOUNT_RT|"50%"|N|0.5||.|
42  |1|DISCOUNT_RT|"40%"|N|0.4||0|
43  |1|DISCOUNT_RT|"30%"|N|0.3||1|
44  |1|CURRENCY_SYMBOL|"GBP"|C||"GBP"|.|
45  |1|CURRENCY_SYMBOL|"RSD"|C||"RSD"|.|
46  |2|DISCOUNT_RT|"50%"|N|0.5||.|
47  |2|DISCOUNT_RT|"40%"|N|0.4||1|
48  |2|CURRENCY_SYMBOL|"EUR"|C||"EUR"|.|
49  |2|CURRENCY_SYMBOL|"HKD"|C||"HKD"|1|
50 
51  <h4> SAS Macros </h4>
52  @li dc_getlibs.sas
53 
54 
55 **/
56 
57 
58 /* send back the raw and formatted values */
59 %let tgtlib=0;
60 %let varlibds=%mf_getuniquename();
61 %let vartgtlib=%mf_getuniquename();
62 %let var_is_lib=%mf_getuniquename();
63 data _null_;
64  length &varlibds $41 &vartgtlib $8;
65  set work.source_row;
66  &varlibds=upcase(symget('libds'));
67  if &varlibds="&mpelib..MPE_TABLES" then &vartgtlib=LIBREF;
68  else putlog "something unexpected happened";
69 
70  /* validate name */
71  if nvalid(&vartgtlib,'v7') then call symputx('tgtlib',&vartgtlib);
72  call symputx('vartgtlib',&vartgtlib);
73 
74  putlog (_all_)(=);
75 run;
76 
77 %mp_abort(iftrue= ("&tgtlib" ="0" )
78  ,mac=&_program..sas
79  ,msg=%str(Invalid library - %superq(vartgtlib))
80  ,errds=work.dc_error_response
81 )
82 
83 %dc_assignlib(READ,&tgtlib)
84 
85 
86 proc sql;
87 create table work.source as
88  select upcase(memname) as memname
89  ,upcase(name) as name
90  ,type
91  from dictionary.columns
92  where libname="&TGTLIB"
93  and memtype='DATA';
94 
95 create table work.members as
96  select distinct memname as display_value
97  from work.source;
98 
99 data work.DYNAMIC_VALUES;
100  set work.members;
101  raw_value=display_value;
102  display_index=_n_;
103 run;
104 
105 proc sql;
106 create table work.dynamic_extended_values as
107  select a.display_index
108  ,b.name as display_value
109  ,"C" as display_type
110  ,b.name as RAW_VALUE_CHAR
111  ,. as RAW_VALUE_NUM
112  from work.dynamic_values a
113  left join work.source b
114  on a.display_value=b.memname
115  where b.type='num';
116 
117 data work.dynamic_extended_values;
118  set work.DYNAMIC_EXTENDED_VALUES;
119  extra_col_name='VAR_PROCESSED';output;
120  extra_col_name='VAR_TXFROM';output;
121  extra_col_name='VAR_TXTO';output;
122  extra_col_name='VAR_BUSFROM';output;
123  extra_col_name='VAR_BUSTO';output;
124 run;
125 /* set some force flags */
126 data work.dynamic_extended_values;
127  set work.DYNAMIC_EXTENDED_VALUES;
128  forced_value=0;
129  if extra_col_name='VAR_TXFROM' & raw_value_char='TX_FROM' then forced_value=1;
130  if extra_col_name='VAR_TXTO' & raw_value_char='TX_TO' then forced_value=1;
131 run;
132 
133 proc sort;
134  by extra_col_name display_index;
135 run;