Help using Base SAS procedures

Merging and missing variables

Reply
Contributor
Posts: 57

Merging and missing variables

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

Super User
Posts: 17,819

Re: Merging and missing variables

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

Contributor
Posts: 57

Re: Merging and missing variables

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

Super User
Posts: 17,819

Re: Merging and missing variables

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.

Super User
Posts: 10,500

Re: Merging and missing variables

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

Contributor
Posts: 57

Re: Merging and missing variables

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

Ask a Question
Discussion stats
  • 5 replies
  • 212 views
  • 6 likes
  • 3 in conversation