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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.