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