Identify first occurrence based on different keys

CP2 — Tue, 18 Jun 2024 20:43:56 GMT

I had to match two files based on 4 different keys from most strict to loose to add a cost variable. I did this in steps using proc sql and created 4 different tables. Then I appended the tables together with the non-matches so that I had the table I started with but with match_flag1, match_flag2, match_flag3, match_flag4. I only want to count the first occurrence of the data that I retrieved from the other table based on the hierarchy of best match. I can't figure out how to do this and keep the original data. I feel like Hash Add() could do this if I output to 4 different tables and add them together again but I need to keep all the data in place so that duplicated reasoning can be maintained for each row. In other words I cannot output a separate table. I just want to update the value if it is the first occurrence of the match. For example, if match_flag3=1 for one row of the key and match_flag4=1 for a diffferent row, I only want to keep the value in the row where match_flag3 =1 for that key. I tried doing this with first. and last. logic but becuase there are 4 different types of matches I could not get the right sort and logic.

The following logic almost gets me there but there could still be duplicates keys when match_flag# variables =1 so I need to still identify the first occurrence of the key within the following logic.

data test;

if _N_ = 1 then do;

declare hash m1 (dataset: "Have");

m1.defineKey("id", "dos", "diag", "provider", "lob");

m1.defineData("match_flag1");

m1.defineDone();

declare hash m2 (dataset: "Have");

m2.defineKey("id", "dos", "diag", "provider");

m2.defineData("match_flag2");

m2.defineDone();

declare hash m3 (dataset: "Have");

m3.defineKey("id", "dos", "diag", "lob");

m3.defineData("match_flag3");

m3.defineDone();

declare hash m4 (dataset: "Have");

m4.defineKey("id", "dos", "diag");

m4.defineData("match_flag4");

m4.defineDone();

end;

set Have (drop=match_flag1 match_flag2 match_flag3 match_flag4 );

length match_flag1 8. match_flag2 8. match_flag3 8. match_flag4 8.;

if m1.find() = 0 then do;

value_revised =value;

match_keep=1;

end;

else if m2.find()=0 then do;

value_revised = value;

match_keep=1;

end;

else if m3.find()=0 then do;

value_revised=value;

match_keep=1;

end;

else if m4.find()=0 then do;

value_revised =value;

match_keep=1;

end;

else do;

match_keep= 0;

value_revised = 0;

end;

;

run;

Re: Identify first occurrence based on different keys

ballardw — Tue, 18 Jun 2024 21:14:30 GMT

It seems sort of obvious that if you have multiple variables (???) then perhaps combine them or set the value of a single variable to 1,2,3 and 4 then sort the data by your identification variables and that new variable.

I would think first. logic would then make sense. (If this were my data knowing this was a later step I might of set that "order of priority" or whatever at each stage when combining the data sets.

But no example data provided so can't be sure.

Re: Identify first occurrence based on different keys

mkeintz — Tue, 18 Jun 2024 21:37:03 GMT

I for one, need sample data in the form of a working data step, and also showing what the result should look like. The sample data preferably would have an instance of the problem of duplicates you are trying to manage.

Question: are data items in the hash objects expected to satisfy a match more than once?

topic Re: Identify first occurrence based on different keys in SAS Programming

Identify first occurrence based on different keys

Re: Identify first occurrence based on different keys

Re: Identify first occurrence based on different keys