Compare a variable in two different data-sets in SAS

Question

I need to compare two variables in two different data-sets which has different names for the variables, then i need to write the observations in SASYES if the records match else write it to SASNO.

I am retrieving the records from DB2 and renaming the variables.

my sas code

DATA _NULL_;
   SET WORKLIST;
   SET UNITFUNC;
   IF PRIMNUM=CORRPMNM AND MODELCD=MCMODEL THEN DO; 
      FILE SASYES; 
      PUT @01  ANSFACT1 $CHAR7. 
          @09  CORRPMNM $CHAR12.
          @21  MCMODEL  $CHAR8. 
      OUTPUT SASYES; 
   END; 
   ELSE DO; 
      FILE SASNO; 
      PUT  @01  ANSFACT1 $CHAR7. 
           @09  CORRPMNM $CHAR12. 
           @21  MCMODEL  $CHAR8. 
      OUTPUT SASNO;
   END;
RUN;

When i submit the code, all the observations are written to SASNO even when they are few matching observations in both the data-sets. Please help me.

Note: I have had used MERGE also to read the data from two tables, the result is same.

Can anyone help ?

It would help if you could create small datasets from WORKLIST and UNITFUNC, that includes at least one example of when it works as you expect and at leas one example of where it does not. — D. Josefsson
This question covers the same issue, but with some alterations: stackoverflow.com/questions/29273568/… Take a look if this is suitable for you as well. — D. Josefsson
Yes. But i have to check condition for three variables. If all the three variables match only then it has to write to the file; you see this is where it gets complicated. — sachin kalekar
You are only comparing two variables in the code. But nonetheless, by extending the join in the answer that I pointed you to, you should be able to get the desired result. And if you add some example datasets on what you have and what you want, it will probably be quite easy to show the actual code as well. — D. Josefsson
Thank You. It is working now. But i will get only true data. How would i get the records which do not have matching records. Should i try same query but instead of 'equals', could i use 'notequals'? — sachin kalekar

Joe Joe · Accepted Answer · 2015-04-20T14:01:55

SET takes one row at a time, never comparing across datasets. Like taking one stack of cards and setting it on top of another, then taking one card at a time.

MERGE combines two datasets, so you take two stacks of cards and place them side by side and look at the top from each pile at one time. So that's what you want to do here: merge them.

data compare;
  merge ds1 ds2;
  by commonID;
run;

You might want to merge by an ID variable, or you might want to merge without a by statement to just compare the first record from each dataset, the second from each dataset, etc.

data compare;
  merge ds1 ds2;
run;

You can add code in that data step to further limit what is output, or you can just merge into one dataset then look at it visually and create additional code afterwards.

Compare a variable in two different data-sets in SAS

1 Answers