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
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.
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?
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
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
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).
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.
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
Hash Table is a good choice.
What output result do you want.
Ksharp
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.