Merging and missing variables

[SannaSanna]

Hello-

I have been trying to merge two large datasets but having difficulty retaining all the variables.  This has typically been my ‘go to’ method:

PROC SORT DATA=ALL OUT=SOURCE NODUPKEY DUPOUT=XX; BY REGIONID LOCATIONID USERID ;RUN; 

PROC SORT DATA=REFERENCETAB NODUPKEY OUT=ID ; BY REGIONID LOCATIONID USERID;RUN; 

DATA YFYS YFNS NFYS;

      MERGE SOURCE(in=a) ID(in=b); BY REGIONID LOCATIONID USERID;

      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

I am retaining A=B and A=1 and B=0. However, I am missing some variables from my ‘Source’ dataset after the merge.  I cant understand why?

I’ve also used Pro SQL for merging, but I need to control for record counts and am a bit uneasy with this method.  Can someone give me some insight?  Thank young

[Reeza]

Is your code missing some BY's or is that just a copy/paste error?

PROC SORT DATA=ALL OUT=SOURCE NODUPKEY DUPOUT=XX; BY REGIONID LOCATIONID USERID ;RUN; 

PROC SORT DATA=REFERENCETAB NODUPKEY OUT=ID ; BY REGIONID LOCATIONID USERID;RUN; 

DATA YFYS YFNS NFYS;

      MERGE SOURCE(in=a) ID(in=b); BY REGIONID LOCATIONID USERID;

      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

[SannaSanna]

Yes.  There was a 'BY'  it was a copy and paste error.  Also the variables in each dataset should be unique so there should not be any over-written data.  Thank you

[Reeza]

Can you provide more details, preferably a proc contents on your input datasets for the merge and the proc contents from the output that is missing variables?

I don't see anything wrong in your code at first glance.

[ballardw]

Actually missing the variables or some variables missing values? The NODUPKEY may not be selecting the record you want/expect.

[SannaSanna]

Thank you.  I removed nodupkey and ran separately and it seemed to have worked.