IF/THEN statement in a many-to-one DATA step MERGE

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

IF/THEN statement in a many-to-one DATA step MERGE

I have used an IF/THEN statement in a many-to-one merge.  I have transaction data over two years and many people have multiple transactions.  I also have a model score that applies only to transactions in the most recent year.  The model score dataset has one record per individual.

Here is my (erroneous) code:

     DATA FILE ;

     MERGE TRANSACTION_DATA  MODELSCORES ;

     BY ID ;

     IF YEAR=2013 THEN SCORE=. ;

     RUN ;

This code results in missing scores for everyone who has a record in 2013 even if they also have a transaction in 2014.

My reading of the documentation is that IF statements are processed AFTER records are brought into the data vector and that therefore the IF statement should only affect those records where YEAR=2013 rather than all records of an individual with any record where YEAR=2013.

Am I missing something?  Does anyone know whether this is documented anywhere?


Accepted Solutions
Solution
‎10-09-2013 10:17 PM
Respected Advisor
Posts: 4,932

Re: IF/THEN statement in a many-to-one DATA step MERGE

Posted in reply to bbenbaruch

The observation from MODELSCORES is read only once and the value of SCORE is retained until the end of BY group processing. If you overwrite it, you loose it. Try this instead:

DATA FILE ;

     MERGE TRANSACTION_DATA  MODELSCORES ;

     BY ID ;

     IF YEAR=2013 THEN NEWSCORE = .;

     ELSE NEWSCORE = SCORE ;

     RUN ;

 

PG

PG

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 83

Re: IF/THEN statement in a many-to-one DATA step MERGE

Posted in reply to bbenbaruch

How about ...


DATA FILE ;

     MERGE TRANSACTION_DATA  MODELSCORES ;

     BY ID ;

     IF YEAR=2013 THEN do;
SCORE=. ;
end;
     RUN ;

Occasional Contributor
Posts: 19

Re: IF/THEN statement in a many-to-one DATA step MERGE

This doesn't work.  It just puts the action inside a DO LOOP.  See below.

Solution
‎10-09-2013 10:17 PM
Respected Advisor
Posts: 4,932

Re: IF/THEN statement in a many-to-one DATA step MERGE

Posted in reply to bbenbaruch

The observation from MODELSCORES is read only once and the value of SCORE is retained until the end of BY group processing. If you overwrite it, you loose it. Try this instead:

DATA FILE ;

     MERGE TRANSACTION_DATA  MODELSCORES ;

     BY ID ;

     IF YEAR=2013 THEN NEWSCORE = .;

     ELSE NEWSCORE = SCORE ;

     RUN ;

 

PG

PG
Occasional Contributor
Posts: 19

Re: IF/THEN statement in a many-to-one DATA step MERGE

PG is correct.  The problem was my partial understanding of SAS data vectors and, if I may be so bold, unclear SAS documentation.

PG is correct about the cause of my problem.  There are several ways this problem can be solved.

I could simply take the IF statement out of my MERGE and then execute an additional DATA step with my IF statement, Simple. Quick if the dataset is relatively small.  Inelegant.  But I would get the results I want.

OR I could create a new variable with my IF statement and DROP the original SCORE and get the results I want.  This is PG's solution.  Simple. Quick. Not so elegant but very straightforward.

OR I could have added YEAR=2014 to all records in my SCORE dataset and MERGED the datasets on two variables -- BY ID SCORE.  Simple. Quick. Not so elegant but very straightforward.

All of these would give me the results I want.

And now I am going to try to figure out how I can label my original question as "answered".

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 205 views
  • 0 likes
  • 3 in conversation