mpe_tables.dsn.sas

Go to the documentation of this file.

/**
  @file
  @brief fetch extended values for DSN
  @details Fetches datasets in a library, and ALSO fetches a list of numeric
  vars for each dataset for use in adjacent columns (such as VAR_PROCESSED,
  TX_TO etc).
 
  Available macro variables:
    @li MPELIB - The DC control library
    @li LIBDS - The library.dataset being filtered
    @li VARIABLE_NM - The column being filtered
 
 
  <h4> Service Outputs </h4>
  Output should be a single table called "work.dynamic_values" in the format
  below.  display_value should always be character, raw_value is unformatted
  character/numeric.
 
  <h5>DYNAMIC_VALUES</h5>
  The RAW_VALUE column may be charactor or numeric.  If DISPLAY_INDEX is not
  provided, it is added automatically.
 
  |DISPLAY_INDEX:best.|DISPLAY_VALUE:$|RAW_VALUE|
  |---|---|---|
  |1|$77.43|77.43|
  |2|$88.43|88.43|
 
  <h5>DYNAMIC_EXTENDED_VALUES</h5>
  This table is optional.  If provided, it will map the DISPLAY_INDEX from the
  DYNAMIC_VALUES table to additional column/value pairs, that will be used to
  populate dropdowns for _other_ cells in the _same_ row.
 
  Should be used sparingly!  The use of large tables here can slow down the
  browser.
 
  The FORCED_VALUE column can be used to force an extended value to be selected
  by default when a particular value is chosen.
 
  |DISPLAY_INDEX:best.|EXTRA_COL_NAME:$32.|DISPLAY_VALUE:$|DISPLAY_TYPE:$1.|RAW_VALUE_NUM|RAW_VALUE_CHAR:$5000|FORCED_VALUE|
  |---|---|---|---|
  |1|DISCOUNT_RT|"50%"|N|0.5||.|
  |1|DISCOUNT_RT|"40%"|N|0.4||0|
  |1|DISCOUNT_RT|"30%"|N|0.3||1|
  |1|CURRENCY_SYMBOL|"GBP"|C||"GBP"|.|
  |1|CURRENCY_SYMBOL|"RSD"|C||"RSD"|.|
  |2|DISCOUNT_RT|"50%"|N|0.5||.|
  |2|DISCOUNT_RT|"40%"|N|0.4||1|
  |2|CURRENCY_SYMBOL|"EUR"|C||"EUR"|.|
  |2|CURRENCY_SYMBOL|"HKD"|C||"HKD"|1|
 
  <h4> SAS Macros </h4>
  @li dc_getlibs.sas
 
 
**/
 
 
/* send back the raw and formatted values */
%let tgtlib=0;
%let varlibds=%mf_getuniquename();
%let vartgtlib=%mf_getuniquename();
%let var_is_lib=%mf_getuniquename();
data _null_;
  length &varlibds $41 &vartgtlib $8;
  set work.source_row;
  &varlibds=upcase(symget('libds'));
  if &varlibds="&mpelib..MPE_TABLES" then &vartgtlib=LIBREF;
  else putlog "something unexpected happened";
 
  /* validate name */
  if nvalid(&vartgtlib,'v7') then call symputx('tgtlib',&vartgtlib);
  call symputx('vartgtlib',&vartgtlib);
 
  putlog (_all_)(=);
run;
 
%mp_abort(iftrue= ("&tgtlib" ="0" )
  ,mac=&_program..sas
  ,msg=%str(Invalid library - %superq(vartgtlib))
  ,errds=work.dc_error_response
)
 
%dc_assignlib(READ,&tgtlib)
 
 
proc sql;
create table work.source as
  select upcase(memname) as memname
    ,upcase(name) as name
    ,type
  from dictionary.columns
  where libname="&TGTLIB"
    and memtype='DATA';
 
create table work.members as
  select distinct memname as display_value
  from work.source;
 
data work.DYNAMIC_VALUES;
  set work.members;
  raw_value=display_value;
  display_index=_n_;
run;
 
proc sql;
create table work.dynamic_extended_values as
  select a.display_index
    ,b.name as display_value
    ,"C" as display_type
    ,b.name as RAW_VALUE_CHAR
    ,. as RAW_VALUE_NUM
  from work.dynamic_values a
  left join work.source b
  on a.display_value=b.memname
  where b.type='num';
 
data work.dynamic_extended_values;
  set work.DYNAMIC_EXTENDED_VALUES;
  extra_col_name='VAR_PROCESSED';output;
  extra_col_name='VAR_TXFROM';output;
  extra_col_name='VAR_TXTO';output;
  extra_col_name='VAR_BUSFROM';output;
  extra_col_name='VAR_BUSTO';output;
run;
/* set some force flags */
data work.dynamic_extended_values;
  set work.DYNAMIC_EXTENDED_VALUES;
  forced_value=0;
  if extra_col_name='VAR_TXFROM' & raw_value_char='TX_FROM' then forced_value=1;
  if extra_col_name='VAR_TXTO' & raw_value_char='TX_TO' then forced_value=1;
run;
 
proc sort;
  by extra_col_name display_index;
run;