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
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
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
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.
Actually missing the variables or some variables missing values? The NODUPKEY may not be selecting the record you want/expect.
Thank you. I removed nodupkey and ran separately and it seemed to have worked.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.