Macro code for lookup

[David_Billa]

I've  a data as follows in one dataset. Here the mechanism which I want to perform is, CURRENT_ID should be available in RISK_CURVE dataset and INSURANCE_CON. In similar way, GROUP_ID should be available in INSURANCE_CON_GRP and INSURANCE_CON dataset and so on. Here the base dataset is INSURANCE_CON.

In case if there are some values which are not in base table (INSURANCE_CON) but in reference table (INSURANCE_CON_GRP, RISK_CURVE)  then I've to capture that record in the Output.

Checks	TBL_NM
CURRENT_ID should be available in RISK_CURVE	INSURANCE_CON
GROUP_ID should be in INSURANCE_CON_GRP	INSURANCE_CON
LOCK_ID should be available in RISK_CURVE	INSURANCE_CON

Output which I'm looking for,

Fault_record_in_reference_table

1,M,Joseph,234

11,F,Nakma,236

Although this mechanism sounds like a look up, challenge which I have is writing the this validation  checks in macro as I've to insert this code in other macro code. Would be grateful if it can be accomplished with minimal code.

[Kurt_Bremser]

Please supply example data in usable form (data step(s) with datalines).

If you want to do multiple lookups in one pass, hash objects should be the way to go (provided that the lookup data fits into available memory).

[David_Billa]

Could you please help me with one sample code for one look up?

[Kurt_Bremser]

See an example using sashelp.class:

data lookup;
input name $;
datalines;
Alfred
Jane
;

data want;
set sashelp.class;
if _n_ = 1
then do;
  declare hash lookup(dataset:"lookup");
  lookup.definekey("name");
  lookup.definedone();
end;
if lookup.find() = 0;
run;

[David_Billa]

Well, my another question is how can we achieve it without using hashing?

[Kurt_Bremser]

You could try formats, but those have inferior performance compared to hashes; other methods require sorts and multiple passes, or a hefty SQL (which does implicit sorts).

[David_Billa]

Got it! Could you please point me to one example where there is more than
one look up using hash or similar methods?

[Kurt_Bremser · Accepted solution]

You just add additional hash objects from your tables:

data lookup1;
input name $;
datalines;
Alfred
Jane
;

data lookup2;
input age;
datalines;
14
;

data want;
set sashelp.class;
if _n_ = 1
then do;
  declare hash lookup1(dataset:"lookup1");
  lookup1.definekey("name");
  lookup1.definedone();
  declare hash lookup2(dataset:"lookup2");
  lookup2.definekey("age");
  lookup2.definedone();
end;
if lookup1.find() = 0 and lookup2.find() = 0;
run;

[Reeza]

This looks like data quality management and not quite a look up problem. I'd probably recommend using SAS DI if you have it, or building a rules system that makes sense for you data. If your checks are all formated in the same way this isn't too difficult but it's definitely a project, not a forum solution.