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.
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;
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).
Could you please help me with one sample code for one look up?
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;
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).
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.