BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Paul_NYS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

8 REPLIES 8
snoopy369
Barite | Level 11

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?

Haikuo
Onyx | Level 15

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

DaveBirch
Obsidian | Level 7

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

snoopy369
Barite | Level 11

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

Sudhakar_A
Calcite | Level 5

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.

UrvishShah
Fluorite | Level 6

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

Ksharp
Super User

Hash Table is a good choice.

What output result do you want.

Ksharp

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 28235 views
  • 4 likes
  • 8 in conversation