topic Macro code for lookup in SAS Programming

Macro code for lookup

David_Billa — Mon, 18 Nov 2019 12:02:58 GMT

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.

Re: Macro code for lookup

Kurt_Bremser — Mon, 18 Nov 2019 12:20:43 GMT

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).

Re: Macro code for lookup

David_Billa — Mon, 18 Nov 2019 12:32:05 GMT

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

Re: Macro code for lookup

Kurt_Bremser — Mon, 18 Nov 2019 12:36:01 GMT

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;

Re: Macro code for lookup

David_Billa — Mon, 18 Nov 2019 13:51:18 GMT

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

Re: Macro code for lookup

Kurt_Bremser — Mon, 18 Nov 2019 14:58:50 GMT

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).

Re: Macro code for lookup

David_Billa — Mon, 18 Nov 2019 16:50:18 GMT

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

Re: Macro code for lookup

Kurt_Bremser — Mon, 18 Nov 2019 16:55:48 GMT

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;

Re: Macro code for lookup

Reeza — Mon, 18 Nov 2019 17:05:52 GMT

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.