- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hash Table is a good choice.
What output result do you want.
Ksharp
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.