topic merging two datasets with uncommon variables in SAS Procedures

merging two datasets with uncommon variables

lnb001 — Thu, 12 May 2011 15:11:56 GMT

I am trying to merge table 1 with table 2; however, table 1 has a visit # field and lab date fields, while table 2 only has an event date field. I want the merged table to show all labs that have a specific value at a certain visit and also include any corresponding events that happen on that same day or close to that date. The problem is an event date might be different than a lab date. Currently my table looks like this:

VISNO LAB EVENT
00 aa, bb xxxxxxxx
01 aa, ab xxxxxxxx

Because I can't merge by a common variable other that patient ID, the EVENT field repeats whenever there is more than one lab record. What I really want the table to look like is this:

VISNO LAB EVENT
00 aa, bb xxxxxxxx
01 aa, ab

Is there anyway to keep the events from repeating whenever there are multiple lab records?

Thanks.

Re: merging two datasets with uncommon variables

Ksharp — Fri, 13 May 2011 09:41:49 GMT

It would be helpful that you need to post some dummy data and what you want your output to look like. It is hard to guess your intention.

Ksharp

Re: merging two datasets with uncommon variables

Doc_Duke — Fri, 13 May 2011 17:58:57 GMT

I agree with KSharp, the narrative and the examples are confusing. Particularly as the narrative seems to be missing variables and you have 3 heading and 4 pieces of data in the sample.

Reading between the lines, I generally attack this sort of problem with PROC SQL. The idea of a "fuzzy" match or "nearest" match on dates is a natural for the BETWEEN operator on a JOIN. There are good examples of this in the SQL reference manual as well as in several BBUs.

Doc Muhlbaier
Duke