topic Re: Data Validity Check Using Another Table in SAS Programming

Data Validity Check Using Another Table

can1112 — Wed, 01 Mar 2023 06:34:40 GMT

Hello,

I'm trying to do a validity check on table A by checking to see if the data matches values that are determined in table B. The table (B) has two columns, the variable name and values in a long form:

datalines;
Client_Type 1
Client_Type 2
Client_Type 3
Client_Vendor 1
Client_Vendor 2
Client_Vendor 3
;

What is a good way to check that the same variable will conform to those values in table B? Since there are 200 variables, is there a way to create a macro to do this more efficiently?

Thanks in advance for your time!

Re: Data Validity Check Using Another Table

Patrick — Wed, 01 Mar 2023 08:57:54 GMT

You will need to put in more work to make below really useful but it should give you the main idea how to approach this.

You will likely need a separate lookup table for character and numerical columns and you will certainly need two hashes and two arrays. Below only for numerical columns.

I normally create an error and an exception table in such cases. The error table stores all rows with a least one failed error, the exception table got a row per failed DQ check. Error and Exception table can be linked over source row number.

data lookup;
  infile datalines4 truncover;
  input __varname :$upcase32. __value :best32.;
  datalines;
Client_Type 1
Client_Type 2
Client_Type 3
Client_Vendor 1
Client_Vendor 2
Client_Vendor 3
;

data have;
  infile datalines4 truncover dsd;
  input client_type client_vendor;
datalines;
1,3
3,3
4,1
1,4
4,4
;

data comply fail;

  set have;
  array _nvars {*} _numeric_;

  if _n_=1 then
    do;
      if 0 then set lookup(keep=__varname __value);
      dcl hash h1(dataset:'lookup');
      h1.defineKey('__varname','__value');
      h1.defineDone();
    end;
  call missing(of __varname,__value);
  drop _:;

  do _i=1 to dim(_nvars);
    if h1.check(key:upcase(vname(_nvars[_i])),key:_nvars[_i]) ne 0 then 
      do;
        output fail;
        return;
      end;
  end;
  output comply;
run;

proc print data=comply;
run;
proc print data=fail;
run;

Re: Data Validity Check Using Another Table

can1112 — Thu, 02 Mar 2023 14:21:55 GMT

Thank you so much for the reply Patrick. This was exactly what I was looking for even though some of the logic was foreign to me. I really appreciate that you took the time to help! Have a great day!