topic Re: identifying the earliest event in SAS Programming

identifying the earliest event

niam — Mon, 26 Nov 2012 00:16:19 GMT

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

Re: identifying the earliest event

PGStats — Mon, 26 Nov 2012 00:36:18 GMT

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;

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

Re: identifying the earliest event

Haikuo — Mon, 26 Nov 2012 02:21:44 GMT

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;

Re: identifying the earliest event

Ksharp — Mon, 26 Nov 2012 06:44:31 GMT

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

Re: identifying the earliest event

Astounding — Mon, 26 Nov 2012 17:51:22 GMT

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.