demodata.sas

Go to the documentation of this file.

/**
  @file
  @brief Creates demo tables and associated config
  @details Can be removed in prod installs.
 
  To activate this job, add the following to SETTINGS:
 
      %let demolib=PUBLIC;
      libname &demolib "%sysfunc(pathname(&dc_libref))/&demolib";
      %let joblib=HOOKLIB;
      libname &joblib "%sysfunc(pathname(&dc_libref))/&joblib";
      %let dcdemoflag=1;
 
  Note that this will:
    * REPLACE any tables named CARS_EXT or COUNTRIES in the PUBLIC library
    * REPLACE all DC config for libraries named PUBLIC
    * CREATE a folder called "demo" in the DC Apploc
    * CREATE two BASE libraries (HOOKLIB & PUBLIC) in the DC (physical) folder
 
  <h4> SAS Macros </h4>
  @li mpeinit.sas
  @li mf_getengine.sas
  @li mf_getuser.sas
  @li mf_increment.sas
  @li mf_nobs.sas
  @li mf_uid.sas
  @li mp_abort.sas
  @li mp_binarycopy.sas
  @li mp_replace.sas
  @li mx_createjob.sas
 
  @author 4GL Apps Ltd
  @copyright 4GL Apps Ltd.  This code may only be used within Data Controller
    and may not be re-distributed or re-sold without the express permission of
    4GL Apps Ltd.
 
**/
 
%let dcdemoflag=0;
options dlcreatedir;
%global joblib demolib;
%mpeinit()
 
%mp_abort(iftrue= (&dcdemoflag ne 1)
  ,mac=&_program
  ,msg=%str(Job not configured.  See comments in the code.)
)
 
data work.cars_ext(index=(carspk=(make model PRODUCTIONDATE) /unique));
  attrib
    MAKE                             length= $13
    MODEL                            length= $40
    TYPE                             length= $8
    ORIGIN                           length= $6
    COUNTRY                          length= $30
    POTENTIALBUY                     length= $6
    COMMENT                          length= $30
    NOTES                            length= $30
    CHECKBOXVAR                      length= $3
    PRODUCTIONDATE                   length= 8      format=DATE9.
  ;
  set sashelp.cars;
  retain comment 'n/a';
  if mod(ceil(ranuni(1)*100),3)=0 then notes=catx(' ',make,type);
  call missing(notes);
  /* random / reproducible date between 1960 and 2020 */
  PRODUCTIONDATE=ceil(ranuni(1)*365*60);
  if mod(ceil(ranuni(1)*1000),2)=0 then CHECKBOXVAR='YES';
  else CHECKBOXVAR='No';
  if mod(ceil(ranuni(1)*1000),3)=0 then POTENTIALBUY='Maybe';
  else if mod(ceil(ranuni(1)*1000),2)=0 then POTENTIALBUY='Yes';
  else POTENTIALBUY='No';
  make=cats(make);
  model=cats(model);
 
  array cntrs (4) $ 60 _temporary_ ( "Germany" "France" "Poland" "Italy");
  if origin='USA' then country='USA';
  else if origin='Asia' then do;
    if mod(_n_,2)=0 then country='Japan';
    else country='Korea';
  end;
  else COUNTRY = cntrs[ ceil(dim(cntrs) * ranuni(1))];
 
  *put (_all_)(=);
run;
 
data work.COUNTRIES(index=(countriespk=(origin country) /unique));
  attrib
    ORIGIN                           length= $6
    COUNTRY                          length= $30
  ;
  infile cards dsd;
  input
    ORIGIN                           :$char.
    COUNTRY                          :$char.
  ;
datalines4;
Europe,Germany
Europe,France
Europe,Poland
Europe,Italy
USA,USA
Asia,Japan
Asia,Korea
;;;;
run;
 
data work.jobdata;
  length message job $100;
  call missing(of _all_);
  stop;
run;
 
%let engine_type=%mf_getengine(&demolib);
%put &=engine_type;
%if &engine_type=CAS %then %do;
  proc cas;
    table.tableExists result=r / name="CARS_EXT" caslib="PUBLIC";
    if r.exists then
      table.dropTable / name="CARS_EXT" caslib="PUBLIC" quiet=TRUE;
 
    table.tableExists result=r2 / name="COUNTRIES" caslib="PUBLIC";
    if r2.exists then
      table.dropTable / name="COUNTRIES" caslib="PUBLIC" quiet=TRUE;
 
    table.tableExists result=r2 / name="MPE_AUDIT" caslib="PUBLIC";
    if r2.exists then
      table.dropTable / name="MPE_AUDIT" caslib="PUBLIC" quiet=TRUE;
  quit;
  proc casutil;
    load data=work.CARS_EXT outcaslib="PUBLIC" casout="CARS_EXT" promote;
    load data=work.COUNTRIES outcaslib="PUBLIC" casout="COUNTRIES" promote;
    load data=&dc_libref..MPE_AUDIT
      outcaslib="PUBLIC" casout="MPE_AUDIT" promote;
  run;
  data &joblib..JOBDATA; set work.JOBDATA;run;
%end;
%else %do;
  options replace;
  data &demolib..CARS_EXT; set work.cars_ext;
  data &demolib..COUNTRIES; set work.countries;
  data &joblib..JOBDATA; set work.JOBDATA;run;
%end;
 
%let apploc=%mf_getapploc(&_program);
%let demolib=%upcase(&demolib);
proc sql;
delete from &dc_libref..mpe_tables
  where libref="&demolib" and dsn in ('CARS_EXT','COUNTRIES');
data append;
  if 0 then set &dc_libref..mpe_tables;
  TX_FROM=0;
  TX_TO='31DEC9999:23:59:59'dt;
  LIBREF="&demolib";
  LOADTYPE='UPDATE';
  NUM_OF_APPROVALS_REQUIRED=1;
  PRE_EDIT_HOOK="&apploc/demo/PREEDIT";
  POST_EDIT_HOOK="&apploc/demo/POSTEDIT";
  PRE_APPROVE_HOOK="&apploc/demo/PREAPPROVE";
  POST_APPROVE_HOOK="&apploc/demo/POSTAPPROVE";
  DSN='CARS_EXT'; BUSKEY='MAKE MODEL PRODUCTIONDATE'; output;
  DSN='COUNTRIES'; BUSKEY='ORIGIN COUNTRY'; output;
run;
proc append base=&dc_libref..MPE_TABLES data=&syslast;
run;
 
/* hard coded values for CHECKBOXVAR */
%let rk=1e6;
proc sql noprint;
delete from &dc_libref..mpe_selectbox
  where select_lib="&demolib"
    and select_ds in ('CARS_EXT');
select max(selectbox_rk) into: rk
  from &dc_libref..mpe_selectbox;
 
insert into &dc_libref..mpe_selectbox set
  selectbox_rk=%mf_increment(rk)
  ,ver_from_dttm=0
  ,select_lib="&demolib"
  ,select_ds="CARS_EXT"
  ,base_column="CHECKBOXVAR"
  ,selectbox_value='Yes'
  ,selectbox_order=1
  ,ver_to_dttm='31DEC5999:23:59:59'dt;
insert into &dc_libref..mpe_selectbox set
  selectbox_rk=%mf_increment(rk)
  ,ver_from_dttm=0
  ,select_lib="&demolib"
  ,select_ds="CARS_EXT"
  ,base_column="CHECKBOXVAR"
  ,selectbox_value='No'
  ,selectbox_order=2
  ,ver_to_dttm='31DEC5999:23:59:59'dt;
 
/* Table driven values */
delete from &dc_libref..MPE_VALIDATIONS
  where base_lib="&demolib" and base_ds="CARS_EXT";
 
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="MAKE"
    ,rule_type='HARDSELECT'
    ,rule_value="SASHELP.CARS.MAKE"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="MODEL"
    ,rule_type='HARDSELECT'
    ,rule_value="SASHELP.CARS.MODEL"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="TYPE"
    ,rule_type='SOFTSELECT'
    ,rule_value="SASHELP.CARS.TYPE"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="POTENTIALBUY"
    ,rule_type='SOFTSELECT'
    ,rule_value="&demolib..CARS_EXT.POTENTIALBUY"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="COMMENT"
    ,rule_type='NOTNULL'
    ,rule_value="n/a"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="ENGINESIZE"
    ,rule_type='MINVAL'
    ,rule_value="1.3"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="ENGINESIZE"
    ,rule_type='MAXVAL'
    ,rule_value="8.3"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&_program
  ,msg=%str(syscc=syscc=&syscc during param configuration)
)
 
/* programmatic values for COUNTRY (Dynamic Dropdown) */
filename vldtr temp;
data _null_;
  file vldtr ;
  put 'proc sql;';
  put 'create table work.vals as';
  put '  select distinct ORIGIN as display_value,';
  put '  ORIGIN as raw_value';
  put "  from &demolib..COUNTRIES";
  put '  order by 1;';
  put 'data work.DYNAMIC_VALUES; set work.vals;display_index=_n_;run;';
  put ' ';
  put 'proc sql;';
  put 'create table work.dev as ';
  put '  select a.display_index,b.country as display_value';
  put '  from work.DYNAMIC_VALUES as a';
  put "  left join &demolib..countries as b";
  put "  on a.raw_value=b.origin";
  put '  order by display_index;';
  put 'data work.DYNAMIC_EXTENDED_VALUES; set work.dev;by display_index;';
  put '  EXTRA_COL_NAME="COUNTRY";';
  put '  DISPLAY_TYPE="C";';
  put '  RAW_VALUE_CHAR=DISPLAY_VALUE;';
  put '  RAW_VALUE_NUM=.;';
  put '  if first.display_index then forced_value=1;';
  put 'run;';
run;
%mx_createjob(path=&apploc/demo
  ,name=origin,code=vldtr
)
proc sql;
insert into &dc_libref..MPE_VALIDATIONS set
    tx_from=0
    ,base_lib="&demolib"
    ,base_ds="CARS_EXT"
    ,base_col="ORIGIN"
    ,rule_type='HARDSELECT_HOOK'
    ,rule_value="&apploc/demo/origin"
    ,rule_active=1
    ,tx_to='31DEC5999:23:59:59'dt;
 
/* PRE_EDIT JOB */
%let fvar=XXXXXXXXXXX; /* cannot substitute macvars in parmcards */
filename ft15f001 temp;
parmcards4;
  proc sql;
  insert into XXXXXXXXXXX.JOBDATA values(
    "&orig_libds (%mf_nobs(work.out) obs) fetched for editing %trim(
    )by %mf_getUser() at %sysfunc(datetime(),datetime19.)","&pgmloc");
;;;;
filename f1 temp;
%mp_binarycopy(inref=ft15f001, outref=f1)
%mp_replace("%sysfunc(pathname(f1))", findvar=fvar, replacevar=joblib)
%mx_createjob(path=&apploc/demo,name=PREEDIT,code=f1)
filename ft15f001 clear;
 
/* POST EDIT JOB */
filename ft15f001 temp;
parmcards4;
  proc sql;
  insert into XXXXXXXXXXX.JOBDATA values(
    "&orig_libds staged %trim(
    )by %mf_getUser() at %sysfunc(datetime(),datetime19.)","&pgmloc");
;;;;
filename f2 temp;
%mp_binarycopy(inref=ft15f001, outref=f2)
%mp_replace("%sysfunc(pathname(f2))", findvar=fvar, replacevar=joblib)
%mx_createjob(path=&apploc/demo,name=POSTEDIT,code=f2)
filename ft15f001 clear;
 
/* PRE APPROVE JOB */
filename ft15f001 temp;
parmcards4;
  proc sql;
  insert into XXXXXXXXXXX.JOBDATA values(
    "&orig_libds (%mf_nobs(work.staging_ds) obs) under review by %trim(
    )by %mf_getUser() at %sysfunc(datetime(),datetime19.)","&pgmloc");
;;;;
filename f3 temp;
%mp_binarycopy(inref=ft15f001, outref=f3)
%mp_replace("%sysfunc(pathname(f3))", findvar=fvar, replacevar=joblib)
%mx_createjob(path=&apploc/demo,name=PREAPPROVE,code=f3)
filename ft15f001 clear;
 
/* POST APPROVE JOB */
filename ft15f001 temp;
parmcards4;
  proc sql;
  insert into XXXXXXXXXXX.JOBDATA values(
    "&orig_libds (%mf_nobs(work.staging_ds) obs) approved by %trim(
    )by %mf_getUser() at %sysfunc(datetime(),datetime19.)","&pgmloc");
;;;;
filename f4 temp;
%mp_binarycopy(inref=ft15f001, outref=f4)
%mp_replace("%sysfunc(pathname(f4))", findvar=fvar, replacevar=joblib)
%mx_createjob(path=&apploc/demo,name=POSTAPPROVE,code=f4)
filename ft15f001 clear;