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.  For
    numeric columns, SAS date, datetime and time literals are also accepted, eg
    '20APR2042'd, '01JAN1960:00:00:42'dt, '00:00:42't.
 
  @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= (work.badrecords) The output table, which is a copy of the
    &inds. table plus a REASON_CD column, containing only bad records.
    If bad records are found, the SYSCC value will be set to 1008
    (a 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 hex / name literals and exponents in RAW_VALUE field
  @sasjslint {"maxLineLength": 99}
**/
 
%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.
  */
%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 end=last;
  length reason_cd $4032 vtype vtype2 $1 vnum dsid 8 tmp $4000 re_temporal 8
    vref $64;
  drop tmp re_temporal vref;
  retain re_temporal;
  /* SAS temporal literals - date, datetime and time */
  if _n_=1 then re_temporal=prxparse("/^'[^']*'(dt|d|t)$/i");
 
  /* 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 */
  retain dsid;
  if _n_=1 then 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;
      goto endstep;
    end;
    /* now we can get the type */
    else vtype=vartype(dsid,vnum);
  end;
  else do;
      REASON_CD=cats("Could not open &targetds");
      putlog REASON_CD= dsid=;
      call symputx('reason_cd',reason_cd,'l');
      call symputx('nobs',_n_,'l');
      output;
      stop;
  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;
 
  /* CONTAINS is a character-only operator - the engine rejects a numeric
    operand, so catch the pairing here rather than letting the generated
    clause fail validation with no reference to the offending variable */
  if vtype='N' & OPERATOR_NM='CONTAINS' then do;
    vref=cats("&targetds",'.',VARIABLE_NM);
    REASON_CD=catx(' ','CONTAINS is only valid for character variables -'
      ,vref,'is numeric');
    putlog REASON_CD= OPERATOR_NM=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
    return;
  end;
 
  /* the operand must be a string literal or a character variable - the
    engine rejects an unquoted numeric value even where the column is
    character, eg 'name contains 12' */
  if vtype='C' & OPERATOR_NM='CONTAINS'
    & subpad(strip(raw_value),1,1) not in ('"',"'")
  then do;
    vref=cats("&targetds",'.',VARIABLE_NM);
    /* a bare value can only be a variable reference if it is a valid name */
    vnum=0;
    if prxmatch('/^[a-z_][a-z0-9_]*$/i',strip(raw_value))>0 then do;
      vnum=varnum(dsid,strip(raw_value));
    end;
    if vnum>=1 then do;
      /* the name is a variable of this table - the type must be character */
      vtype2=vartype(dsid,vnum);
      if vtype2='C' then return;
      REASON_CD=catx(' ',"Compared Type ("!!vtype2!!") is not (C) -"
        ,vref,'CONTAINS',cats(raw_value));
    end;
    else REASON_CD=catx(' ','CONTAINS requires a quoted value or a character'
      ,'variable -',vref,'CONTAINS',cats(raw_value));
    putlog REASON_CD= OPERATOR_NM=;
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
    return;
  end;
 
  /* special missing logic */
  if vtype='N' & OPERATOR_NM in ('=','>','<','<=','>=','NE','GE','LE') then do;
    if 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;
    else if subpad(upcase(raw_value),1,1) 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;
      /* check if the raw_value contains a valid variable NAME */
      vnum=varnum(dsid,subpad(raw_value,1,32));
      if vnum>0 then do;
        /* now we can get the type */
        vtype2=vartype(dsid,vnum);
        /* check type matches */
        if vtype2=vtype then do;
          /* valid target var - exit loop */
          return;
        end;
        else do;
          REASON_CD=cats("Compared Type (",vtype2,") is not (",vtype,")");
          putlog REASON_CD= dsid=;
          call symputx('reason_cd',reason_cd,'l');
          call symputx('nobs',_n_,'l');
          output;
          goto endstep;
        end;
      end;
    end;
  end;
 
  /* a SAS temporal literal is a valid value for a numeric column */
  if vtype='N' and prxmatch(re_temporal,cats(raw_value))>0 then return;
 
  /* 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;
        /* a SAS temporal literal is a valid value for a numeric column */
        if prxmatch(re_temporal,cats(tmp))=0 then do;
          if OPERATOR_NM ='BETWEEN' and subpad(upcase(tmp),1,1) 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;
            /* check if the raw_value contains a valid variable NAME */
            /* is not valid syntax for IN or NOT IN */
            vnum=varnum(dsid,subpad(tmp,1,32));
            if vnum>0 then do;
              /* now we can get the type */
              vtype2=vartype(dsid,vnum);
              /* check type matches */
              if vtype2 ne vtype then do;
                REASON_CD=cats("Compared Type (",vtype2,") is not (",vtype,")");
                putlog REASON_CD= dsid=;
                call symputx('reason_cd',reason_cd,'l');
                call symputx('nobs',_n_,'l');
                output;
                goto endstep;
              end;
            end;
            else 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;
          end;
          else 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;
        end;
      end;
    end;
    /* every value of a numeric IN / BETWEEN list is checked one by one above,
      so a numeric list skips the generic check below - that would only flag
      the list syntax itself.  A character list is unchanged. */
    if vtype='N' then return;
  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;
    if vtype='C' and subpad(upcase(raw_value),1,1) 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;
      /* check if the raw_value contains a valid variable NAME */
      vnum=varnum(dsid,subpad(raw_value,1,32));
      if vnum>0 then do;
        /* now we can get the type */
        vtype2=vartype(dsid,vnum);
        /* check type matches */
        if vtype2=vtype then do;
          /* valid target var - exit loop */
          return;
        end;
        else do;
          REASON_CD=cats("Compared Char Type (",vtype2,") is not (",vtype,")");
          putlog REASON_CD= dsid=;
          call symputx('reason_cd',reason_cd,'l');
          call symputx('nobs',_n_,'l');
          output;
          goto endstep;
        end;
      end;
    end;
 
    putlog raw_value3= $hex32.;
    REASON_CD=cats('Invalid RAW_VALUE:',raw_value);
    putlog (_all_)(=);
    call symputx('reason_cd',reason_cd,'l');
    call symputx('nobs',_n_,'l');
    output;
  end;
 
  endstep:
  if last then rc=close(dsid);
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;