topic Re: Comparing a variable from one record to the previous record in SAS Programming

Comparing a variable from one record to the previous record

Paul_NYS — Wed, 17 Apr 2013 20:51:38 GMT

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

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

snoopy369 — Wed, 17 Apr 2013 20:55:51 GMT

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?

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

Haikuo — Wed, 17 Apr 2013 21:05:22 GMT

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 Surrender 4/1/13

;

data want;

set have;

by childid;

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

run;

Haikuo

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

Sudhakar_A — Thu, 18 Apr 2013 03:17:11 GMT

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.

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

DaveBirch — Thu, 18 Apr 2013 03:26:28 GMT

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

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

snoopy369 — Thu, 18 Apr 2013 03:44:21 GMT

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).

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

UrvishShah — Thu, 18 Apr 2013 06:35:32 GMT

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

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

Ksharp — Sat, 20 Apr 2013 09:35:14 GMT

Hash Table is a good choice.

What output result do you want.

Ksharp

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

Astounding — Sat, 20 Apr 2013 23:31:57 GMT

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.