topic Re: Merging and missing variables in SAS Procedures

Merging and missing variables

SannaSanna — Tue, 27 Jan 2015 23:22:34 GMT

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

Re: Merging and missing variables

Reeza — Tue, 27 Jan 2015 23:31:55 GMT

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

Re: Merging and missing variables

SannaSanna — Tue, 27 Jan 2015 23:48:06 GMT

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

Re: Merging and missing variables

Reeza — Tue, 27 Jan 2015 23:55:56 GMT

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.

Re: Merging and missing variables

ballardw — Wed, 28 Jan 2015 00:28:10 GMT

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

Re: Merging and missing variables

SannaSanna — Wed, 28 Jan 2015 02:30:48 GMT

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