DATA Step, Macro, Functions and more

identifying the earliest event

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 88
Accepted Solution

identifying the earliest event

I have a data set which contains people and their activity over a period of 4 days. The activity of people changes over these periods.

for example some people have been active from the beginning, some just was active in the last period and some were active in the beginning and the end only.

The data set looks like this :

Person     Activitydate

----------     ---------------

A               1

A               2

A               3

A               4

B               3

B               4

C               1

C               4

D               3

E               4

which means person A has been active in all four days, person B has been active in days 3 and 4. C has been active in day 1 and 3 and D and E has been active in days 3 and 4 respectively

I want to define a new variable such as 'tenure' which shows the number of days that people have been active in days before. So the new data set should look like

Person     Activitydate          Tenure

----------     ---------------           --------

A               1                              0

A               2                              1

A               3                              2

A               4                              3    

B               3                              0

B               4                              1

C               1                              0

C               4                              1    

D               3                              0

E               4                              0

Please note that people C and D will have no prior experience since they started late, and thus the value of tenure will be zero for both of them despite the fact that their activity dates are 3 and 4

Any hints and solutions are greatly appreciated

Thanks for reading this post


Accepted Solutions
Solution
‎11-26-2012 01:44 AM
Super User
Posts: 10,023

Re: identifying the earliest event

Assuming your data has already been sorted.

data x;
input person $ date;
cards;
a 1
a 2
a 3
a 4
b 3
b 4
;
run;
data x;
 set x;
 if person ne lag(person) then tenure=-1;
 tenure+1;
run;

Ksharp

View solution in original post


All Replies
Respected Advisor
Posts: 4,920

Re: identifying the earliest event

If dates are simple numbers as in your example, you can use

proc sql;

create table want as

select person, activityDate, activityDate-min(activityDate) as tenure

from have

group by person;

quit;

PG

This simple query gives the WRONG result. See Haikuo Bians' note below.

PG
Respected Advisor
Posts: 3,156

Re: identifying the earliest event

PG, your solution works best if OP 's request counts calendar days instead of actual activity days. For that, DOW can offer a simple approach:

data want;

  do tenure=0 by 1 until (last.person);

    set have;

      by person notsorted;

      output;

  end;

run;

Haikuo

Update:

If forcing me to use proc sql, I would have to rely on some undocumented feature:

proc sql;

  select *, monotonic()-min(monotonic()) as tunure from have group by person;quit;

Solution
‎11-26-2012 01:44 AM
Super User
Posts: 10,023

Re: identifying the earliest event

Assuming your data has already been sorted.

data x;
input person $ date;
cards;
a 1
a 2
a 3
a 4
b 3
b 4
;
run;
data x;
 set x;
 if person ne lag(person) then tenure=-1;
 tenure+1;
run;

Ksharp

Super User
Posts: 5,503

Re: identifying the earliest event

This isn't all that different from Ksharp's solution.  I just prefer a slightly different set of tools because LAG can be tricky and because this version warns you if the data are not in order.

data want;

  set have;

  by person;

  if first.person then tenure=0;

  else tenure + 1;

run;

Optionally, you could extend the BY statement to verify the order even further:

by person Activitydate;

Good luck.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 219 views
  • 7 likes
  • 5 in conversation