topic Re: Table merging question in SAS Procedures

Table merging question

SannaSanna — Tue, 08 Sep 2020 16:06:06 GMT

Hello!!

I have two large tables I am trying to merge. Table 1 is called services and table 2 is customers. I used the data step merge below and it appears that i am missing some records. There can be multiple customers tied to the same service using the 'merge by unitA' but it seems that the code is picking and merging only on the first instance and missing subsequent customer records which should also be matched too but is missing- this is technically working but I am missing customer records. Can this be correct?

DATA YFYS YFNS NFYS;
MERGE Service1(in=a) customer1(in=b); by unitA;
IF A AND B THEN OUTPUT YFYS;
IF A=1 AND B=0 THEN OUTPUT YFNS;
IF A=0 AND B=1 THEN OUTPUT NFYS; RUN;

Re: Table merging question

ballardw — Tue, 08 Sep 2020 16:21:01 GMT

Example data of both input and the output.

If you multiple records with the same value of UnitA in both data sets then likely the data step merge is not what you want as it was not designed for a many-to-many merge.

Re: Table merging question

SannaSanna — Tue, 08 Sep 2020 17:02:32 GMT

I think thats it. its a one to many merge. What do you suggest as the best way to accomplish this?

Re: Table merging question

mkeintz — Tue, 08 Sep 2020 17:14:53 GMT

@SannaSanna wrote:
I think thats it. its a one to many merge. What do you suggest as the best way to accomplish this?

If it really is a many-to-one merge (which way? many customers to a service -- or vice versa?), then the program you showed will accomplish what you apparently want. You describe the problem as "I am missing some records". That's not enough to make a diagnosis, let alone a prescription.

Please show a sample of the data that become missing after running the merge statement. That's what @ballardw was asking for.

Help us help you.

Re: Table merging question

SannaSanna — Tue, 08 Sep 2020 18:11:42 GMT

Hi.

Below is a sample of the data. I am merging by 'Rx'. My output table (want) is missing the second row for jen.

customer table
customer table	race	Rx
jen	w	b12
mik	h	xyy
rob	b	d20

service table
Rx	ph
b12	wit
b12	wan
o18	chi
xyy	gon
d20	rie

Want
jen	w	b12	wit
jen	w	b12	wan
mik	h	xyy	gon
rob	b	d20	rie

Re: Table merging question

Tom — Tue, 08 Sep 2020 19:54:09 GMT

That does not look like a one to one or one to many relationship. Multiple people could have the same RX. And at least some of the RX have multiple PH values (whatever that is). Use SQL to combine them, it will perform a many to many join.

Also learn how to share your data as simple data steps:

data customer;
  input id $ race $ Rx $;
cards;
jen w b12  
mik h xyy  
rob b d20  
;

data service;
  input Rx $ ph $;
cards; 
b12 wit    
b12 wan    
o18 chi    
xyy gon    
d20 rie
;

data want;
  input id $ race $ rx $ ph $;
cards;
jen w b12 wit
jen w b12 wan
mik h xyy gon
rob b d20 rie
;

Here is code to combine CUSTOMER and SERVICE based on match of RX. I assumed you want all customers even if they do not have any matching service records so used a LEFT JOIN.

proc sql;
create table try as
  select a.id,a.race,a.rx,b.ph
  from customer a
  left join service b
    on a.rx = b.rx 
  order by 1,2,3 
;
quit;

Results:

Obs    id     race    Rx     ph

 1     jen     w      b12    wit
 2     jen     w      b12    wan
 3     mik     h      xyy    gon
 4     rob     b      d20    rie

Re: Table merging question

SannaSanna — Wed, 09 Sep 2020 16:06:29 GMT

Hi. Thank you for your help! My datasets are extremely large and having difficulty with resources and I just provided a brief example of the data. Perhaps the set of data I provided was not a good example, but I have also used Proc SQL to tie the tables together and still missing records. The merge is technically correct using the parameters to merge, however, there are missing join records. Is it true that the SAS PDV runs through the data once and if successful moves on to the next record to merge? What happens if one of my tables have some exact same row records (customer & lots of demographics) and the other table is a table of providers. After merging by 'Rx' (providers Wit & Wan) serve the same customers and possibly on same day. When merging (sorted by provider) it seems the merge is successful for the first sorted provider (Wan) but all those customer records did not merge with the second provider(Wit). Is it that the SAS PDV moves on/finishes after initial succcessful merge even though there are second possible successful merge? I'm stumped.

Re: Table merging question

mkeintz — Wed, 09 Sep 2020 17:32:12 GMT

@SannaSanna

Let's discuss the first problem you identify:

@SannaSanna wrote:
... stuff deleted ...
but I have also used Proc SQL to tie the tables together and still missing records.

"Missing records" is way too amibiguous to diagnose. But from what you have shown us, any missing records are not due to size of the data sets. Please provide an explicit example of a record you expected in the result, but was missing. And provide the initial records from which you expected it to be produced.

You should be able to extract those records into a sample, and then quickly run both your SQL code and DATA step merge code that resulted in missing records.

Show us those codes, and the log file that went with them. Then diagnosis (and maybe prescription) is possible.