DATA Step, Macro, Functions and more

Comparing a variable from one record to the previous record

Accepted Solution Solved
Reply
Regular Contributor
Posts: 216
Accepted Solution

Comparing a variable from one record to the previous record

Hi

Is there a way to compare a variable across records for a given sub-set of records? In the below example I would like to see if the date variable in one record is the same as the same date in the previous record within each of child records.

Paul

ChildID        Disposition         Date

123             TPR                   10/12/12

123             TPR                   2/1/13

123             Surrender           4/1/13

456             TPR                  10/12/12

456             TPR                  2/1/13

456             Surrender          4/1/13


Accepted Solutions
Solution
‎04-20-2013 07:31 PM
Super User
Posts: 5,082

Re: Comparing a variable from one record to the previous record

Are we making a simple problem difficult?  Does this do what you want:

data want;

  set have;

  by child date;

  if first.date then same='N';

   else same='Y';

run;

If this isn't what you want, you might have to give an example or two of what you are looking to get.

Good luck.

View solution in original post


All Replies
Regular Contributor
Posts: 244

Re: Comparing a variable from one record to the previous record

Can you explain more specifically what you're doing?  That dataset doesn't look very useful.  Are you saying if row 2 and row 1 have the same date, or if child 123 and child 456 have the same set of dates?

Respected Advisor
Posts: 3,124

Re: Comparing a variable from one record to the previous record

If last 'date' is the same as the current 'date', then flag=1, otherwise, flag=0. If the first record of each ChildID, reset flag to missing value:

data have;

input (ChildID Disposition Date) (:$);

cards;

123 TPR 10/12/12

123 TPR 2/1/13

123 Surrender 4/1/13

456 TPR 2/1/13

456 TPR 2/1/13

456 Surrender 4/1/13

;

data want;

  set have;

  by childid;

flag=ifn(first.childid, ., ifn(date=lag(date),1,0));

run;

Haikuo

Contributor
Posts: 25

Re: Comparing a variable from one record to the previous record

Hi Haikuo,

Always remember the LAG function doesn't retrieve a value from the previous row in the dataset, but from the previous iteration of the LAG function.  Therefore, if you want a value from the previous row, don't make its execution conditional.  Try this:

data want;

  set have;

  by childid;

  prevdate=lag(date);

  if first.childid then prevdate = . ;

  if date eq prevdate then flag = 1;

  else flag = 0;

  drop prevdate;

run;

Dave

Regular Contributor
Posts: 244

Re: Comparing a variable from one record to the previous record

Dave, while your warning about lag is a good one to keep in mind, IFN evaluates all three conditions each time it is called, so it isn't accurate in this instance.

That said, I prefer the transpose solution - more flexible.

Example of lag with IFN:

data test;

set sashelp.class;

x = ifn(sex='M',ifn(age<13,lag(height),0),0);

put x= age= sex= height=;

run;


You can see the 'lagged' height is the immediately previous record, not the previous male/under 13 record, since each and every record triggers the call to lag (even within two ifn's).

Contributor
Posts: 60

Re: Comparing a variable from one record to the previous record

data t;

     set dt;

     by child disposition;

     if first.disposition then seq=1;

     else seq+1;

run;

proc transpose data=t out=trans;

     by child disposition ;

     var date;

     id seq;

run;

now you will get al the date variables next to next variables so you can compare easily.

Regular Contributor
Posts: 195

Re: Comparing a variable from one record to the previous record

The Dataset which you have posted is not containing any record as per your requirement...And whenever you post query, remember to attach output you want...

Based on your information, you wrote "I would like to see if the date variable in one record is the same as the same date in the previous record within each of child records"...In SAS, record means entire observation not a single object or variable...So rather than posting such a confusing statements try to include sample output that you want...

-Urvish

Super User
Posts: 9,681

Re: Comparing a variable from one record to the previous record

Hash Table is a good choice.

What output result do you want.

Ksharp

Solution
‎04-20-2013 07:31 PM
Super User
Posts: 5,082

Re: Comparing a variable from one record to the previous record

Are we making a simple problem difficult?  Does this do what you want:

data want;

  set have;

  by child date;

  if first.date then same='N';

   else same='Y';

run;

If this isn't what you want, you might have to give an example or two of what you are looking to get.

Good luck.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 6504 views
  • 3 likes
  • 8 in conversation