mp_filtercheck.sas

Go to the documentation of this file.

/**
  @file
  @brief Checks an input filter table for validity
  @details Performs checks on the input table to ensure it arrives in the
  correct format.  This is necessary to prevent code injection.  Will update
  SYSCC to 1008 if bad records are found, and call mp_abort.sas for a
  graceful service exit (configurable).
 
  Used for dynamic filtering in [Data Controller for SAS®](
  https://datacontroller.io).
 
  Usage:
 
      %mp_filtercheck(work.filter,targetds=sashelp.class,outds=work.badrecords)
 
  The input table should have the following format:
 
  |GROUP_LOGIC:$3|SUBGROUP_LOGIC:$3|SUBGROUP_ID:8.|VARIABLE_NM:$32|OPERATOR_NM:$10|RAW_VALUE:$4000|
  |---|---|---|---|---|---|
  |AND|AND|1|AGE|=|12|
  |AND|AND|1|SEX|<=|'M'|
  |AND|OR|2|Name|NOT IN|('Jane','Alfred')|
  |AND|OR|2|Weight|>=|7|
 
  Rules applied:
 
  @li GROUP_LOGIC - only AND/OR
  @li SUBGROUP_LOGIC - only AND/OR
  @li SUBGROUP_ID - only integers
  @li VARIABLE_NM - must be in the target table
  @li OPERATOR_NM - only =/>/</<=/>=/BETWEEN/IN/NOT IN/NE/CONTAINS
  @li RAW_VALUE - no unquoted values except integers, commas and spaces.
 
  @returns The &outds table containing any bad rows, plus a REASON_CD column.
 
  @param [in] inds The table to be checked, with the format above
  @param [in] targetds= The target dataset against which to verify VARIABLE_NM.
    This must be available (ie, the library must be assigned).
  @param [out] abort= (YES) If YES will call mp_abort.sas on any exceptions
  @param [out] outds= The output table, which is a copy of the &inds. table
    plus a REASON_CD column, containing only bad records.  If bad records found,
    the SYSCC value will be set to 1008 (general data problem).  Downstream
    processes should check this table (and return code) before continuing.
 
  <h4> SAS Macros </h4>
  @li mp_abort.sas
  @li mf_getuniquefileref.sas
  @li mf_getvarlist.sas
  @li mf_getvartype.sas
  @li mp_filtergenerate.sas
  @li mp_filtervalidate.sas
 
  <h4> Related Macros </h4>
  @li mp_filtergenerate.sas
  @li mp_filtervalidate.sas
 
  @version 9.3
  @author Allan Bowe
 
  @todo Support date / hex / name literals and exponents in RAW_VALUE field
**/
 
%macro mp_filtercheck(inds,targetds=,outds=work.badrecords,abort=YES);
 
%mp_abort(iftrue= (&syscc ne 0)
  ,mac=&sysmacroname
  ,msg=%str(syscc=&syscc - on macro entry)
)
 
/* Validate input column */
%local vtype;
%let vtype=%mf_getvartype(&inds,RAW_VALUE);
%mp_abort(iftrue=(&abort=YES and &vtype ne C),
  mac=&sysmacroname,
  msg=%str(%str(ERR)OR: RAW_VALUE must be character)
)
%if &vtype ne C %then %do;
  %put &sysmacroname: RAW_VALUE must be character;
  %let syscc=42;
  %return;
%end;
 
 
/**
  * Sanitise the values based on valid value lists, then strip out
  * quotes, commas, periods and spaces.
  * Only numeric values should remain
  */
%local reason_cd nobs;
%let nobs=0;
data &outds;
  /*length GROUP_LOGIC SUBGROUP_LOGIC $3 SUBGROUP_ID 8 VARIABLE_NM $32
    OPERATOR_NM $10 RAW_VALUE $4000;*/
  set &inds;
  length reason_cd $4032 vtype $1 vnum dsid 8 tmp $4000;
  drop tmp;
 
  /* quick check to ensure column exists */
  if upcase(VARIABLE_NM) not in
  (%upcase(%mf_getvarlist(&targetds,dlm=%str(,),quote=SINGLE)))
  then do;
    REASON_CD="Variable "!!cats(variable_nm)!!" not in &targetds";
    putlog REASON_CD= VARIABLE_NM=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
    return;
  end;
 
  /* need to open the dataset to get the column type */
  dsid=open("&targetds","i");
  if dsid>0 then do;
    vnum=varnum(dsid,VARIABLE_NM);
    if vnum<1 then do;
      /* should not happen as was also tested for above */
      REASON_CD=cats("Variable (",VARIABLE_NM,") not found in &targetds");
      putlog REASON_CD= dsid=;
      call symputx('reason_cd',reason_cd,'l');
      call symputx('nobs',_n_,'l');
      output;
      return;
    end;
    /* now we can get the type */
    else vtype=vartype(dsid,vnum);
  end;
 
  /* closed list checks */
  if GROUP_LOGIC not in ('AND','OR') then do;
    REASON_CD='GROUP_LOGIC should be AND/OR, not:'!!cats(GROUP_LOGIC);
    putlog REASON_CD= GROUP_LOGIC=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
  if SUBGROUP_LOGIC not in ('AND','OR') then do;
    REASON_CD='SUBGROUP_LOGIC should be AND/OR, not:'!!cats(SUBGROUP_LOGIC);
    putlog REASON_CD= SUBGROUP_LOGIC=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
  if mod(SUBGROUP_ID,1) ne 0 then do;
    REASON_CD='SUBGROUP_ID should be integer, not '!!cats(subgroup_id);
    putlog REASON_CD= SUBGROUP_ID=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
  if OPERATOR_NM not in
  ('=','>','<','<=','>=','NE','GE','LE','BETWEEN','IN','NOT IN','CONTAINS')
  then do;
    REASON_CD='Invalid OPERATOR_NM: '!!cats(OPERATOR_NM);
    putlog REASON_CD= OPERATOR_NM=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
 
  /* special missing logic */
  if vtype='N'
    and OPERATOR_NM in ('=','>','<','<=','>=','NE','GE','LE')
    and cats(upcase(raw_value)) in (
      '.','.A','.B','.C','.D','.E','.F','.G','.H','.I','.J','.K','.L','.M','.N'
      '.N','.O','.P','.Q','.R','.S','.T','.U','.V','.W','.X','.Y','.Z','._'
    )
  then do;
    /* valid numeric - exit data step loop */
    return;
  end;
 
  /* special logic */
  if OPERATOR_NM in ('IN','NOT IN','BETWEEN') then do;
    if OPERATOR_NM='BETWEEN' then raw_value1=tranwrd(raw_value,' AND ',',');
    else do;
      if substr(raw_value,1,1) ne '('
      or substr(cats(reverse(raw_value)),1,1) ne ')'
      then do;
        REASON_CD='Missing start/end bracket in RAW_VALUE';
        putlog REASON_CD= OPERATOR_NM= raw_value= raw_value1= ;
        call symputx('reason_cd',reason_cd,'l');
        call symputx('nobs',_n_,'l');
        output;
      end;
      else raw_value1=substr(raw_value,2,max(length(raw_value)-2,0));
    end;
    /* we now have a comma seperated list of values */
    if vtype='N' then do i=1 to countc(raw_value1, ',')+1;
      tmp=scan(raw_value1,i,',');
      if cats(tmp) ne '.' and input(tmp, ?? 8.) eq . then do;
        REASON_CD='Non Numeric value provided';
        putlog REASON_CD= OPERATOR_NM= raw_value= raw_value1= ;
        call symputx('reason_cd',reason_cd,'l');
        call symputx('nobs',_n_,'l');
        output;
      end;
      return;
    end;
  end;
  else raw_value1=raw_value;
 
  /* remove nested literals eg '' */
  raw_value1=tranwrd(raw_value1,"''",'');
 
  /* now match string literals (always single quotes) */
  raw_value2=raw_value1;
  regex = prxparse("s/(\').*?(\')//");
  call prxchange(regex,-1,raw_value2);
 
  /* remove commas and periods*/
  raw_value3=compress(raw_value2,',.');
 
  /* output records that contain values other than digits and spaces */
  if notdigit(compress(raw_value3,' '))>0 then do;
    putlog raw_value3= $hex32.;
    REASON_CD=cats('Invalid RAW_VALUE:',raw_value);
    putlog REASON_CD= raw_value= raw_value1= raw_value2= raw_value3=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
 
run;
 
 
data _null_;
  set &outds end=last;
  putlog (_all_)(=);
run;
 
%mp_abort(iftrue=(&abort=YES and &nobs>0),
  mac=&sysmacroname,
  msg=%str(Data issue: %superq(reason_cd))
)
 
%if &nobs>0 %then %do;
  %let syscc=1008;
  %return;
%end;
 
/**
  * syntax checking passed but it does not mean the filter is valid
  * for that we can run a proc sql validate query
  */
%local fref1;
%let fref1=%mf_getuniquefileref();
%mp_filtergenerate(&inds,outref=&fref1)
 
/* this macro will also set syscc to 1008 if any issues found */
%mp_filtervalidate(&fref1,&targetds,outds=&outds,abort=&abort)
 
%mend mp_filtercheck;