topic Re: Merging Issue in SAS Programming

Merging Issue

moreilly — Wed, 29 Mar 2017 16:54:25 GMT

I am working with Medicare claims data and am trying to merge two datasets (TRIGGERS and CLAIMS). TRIGGERS has the variables bene_id, claim_id and date. In TRIGGERS there is one claim (observation) per bene_id. CLAIMS has bene_id, claim_id, date and diagnostic codes. In CLAIMS, each bene_id may have multiple claims (multiple observations). I want to create a new dataset, LOOKBACK, that keeps only the bene_ids that are in both TRIGGERS and CLAIMS and keeps multiple claims (observations) for every bene_id. I expected the following code to work, but I seemed to lose the claim from TRIGGER. Any suggestions?

proc sort data = out.triggers; by bene_id; run;

proc sort data = work.claims; by bene_id; run;

data lookback;
merge out.triggers (in=a) work.claims (in=b);
by bene_id; if a and b;
run;

Re: Merging Issue

collinelliot — Wed, 29 Mar 2017 17:27:30 GMT

What does the log say? If none of these match you should be getting zero obs due to the "if a and b;" Have you compared the values for bene_id in both data sets to make sure they have any overlap at all?

Re: Merging Issue

Astounding — Wed, 29 Mar 2017 17:31:32 GMT

Your final data set can only have one variable named CLAIM_ID. If it appears in both data sets and you want both, this is one possible change to handle it:

merge out.triggers (in=a rename=(claim_id=trigger_claim_id)) work.claims (in=b);

Re: Merging Issue

moreilly — Wed, 29 Mar 2017 17:40:25 GMT

There is overlap. This is the log message:

NOTE: There were 711292 observations read from the data set TRIGGERS.
NOTE: There were 86214334 observations read from the data set CLAIMS.
NOTE: The data set WORK.LOOKBACK has 23448038 observations and 17 variables.

But, when I look at the data, the date from the TRIGGER observation has been lost.

Re: Merging Issue

moreilly — Wed, 29 Mar 2017 18:13:34 GMT

That did it! Thank you!