Solved
Established User
Posts: 1

# Dif is going back two rows on the second observation

I cannot figure out why the dif function is going back 2 rows in the second observation.  It works for the first observation and all subsequent observations.  In the 2nd and fourth row the dif function is not calculating the value I would like.  I am sure that my lack of understanding of first is the culprit, but I can't figure it out.  (Row 2 difference should be 0, Row 4 difference should be 1)

Any help is greatly appreciated (I use SAS EG 7.1, SAS 9.4).

Output data

 ClaimNum Participant_Name TX_DOS difference CLAIM1 NAME1 9/13/2016 0 CLAIM1 NAME1 9/13/2016 . CLAIM2 NAME2 8/25/2015 0 CLAIM2 NAME2 8/26/2015 -384 CLAIM2 NAME2 8/27/2015 1

SAS Code

``````DATA EM_LIST_W_DAYS;
set WORK.EM_LIST;

by ClaimNum ParticipantID;
if first.ClaimNum and first.ParticipantID then
difference = 0;
else if first.ClaimNum=0 and first.ParticipantID=1 then
difference = 0;
else if first.ClaimNum=0 and first.ParticipantID=0 then
difference = DIF(TX_DOS);
run;``````

Accepted Solutions
Solution
‎10-12-2017 08:06 PM
Posts: 1,345

## Re: Dif is going back two rows on the second observation

[ Edited ]

DIF updates a queue of differences.  If you only occasionally execute DIF then the sequence of differences are applied only to the corresponding observations.  This program accommodates that property:

``````DATA EM_LIST_W_DAYS;
set WORK.EM_LIST;
by ClaimNum ParticipantID;
dif=ifn(first.paarticipantid,0,dix(tx_dos));
run;``````

Two points:

1. Using "by cliaminum participantid;".  The first. and last. indicators are hierarchical.   All instances of first.claimnum=1 are subsets of the instances in which first.participantid=1.  And (less petinent in your case) every time first.claimnum=1 all the by-variables to its right will have first. indicators set to 1.

2. The IFN function evaluates both outcomes (the 2nd and 3rd arguments), regardless of the results of the first argument.  As a result the DIF function is updated with every observation, even though it is not always assigned to the result variable.

All Replies
Super User
Posts: 6,785

## Re: Dif is going back two rows on the second observation

For DIF to operate on consecutive observations, it must execute on every observation.  So calculate something like this:

temp = dif(TX_DOS);

Then use TEMP in your IF/THEN statements:

else if first.ClaimNum=0 and first.ParticipantID=0 then difference=temp;

Solution
‎10-12-2017 08:06 PM
Posts: 1,345

## Re: Dif is going back two rows on the second observation

[ Edited ]

DIF updates a queue of differences.  If you only occasionally execute DIF then the sequence of differences are applied only to the corresponding observations.  This program accommodates that property:

``````DATA EM_LIST_W_DAYS;
set WORK.EM_LIST;
by ClaimNum ParticipantID;
dif=ifn(first.paarticipantid,0,dix(tx_dos));
run;``````

Two points:

1. Using "by cliaminum participantid;".  The first. and last. indicators are hierarchical.   All instances of first.claimnum=1 are subsets of the instances in which first.participantid=1.  And (less petinent in your case) every time first.claimnum=1 all the by-variables to its right will have first. indicators set to 1.

2. The IFN function evaluates both outcomes (the 2nd and 3rd arguments), regardless of the results of the first argument.  As a result the DIF function is updated with every observation, even though it is not always assigned to the result variable.
☑ This topic is solved.

Discussion stats
• 2 replies
• 199 views
• 1 like
• 3 in conversation