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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

4 REPLIES 4
PGStats
Opal | Level 21

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
Haikuo
Onyx | Level 15

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;

Ksharp
Super User

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

Astounding
PROC Star

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.

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
  • 4 replies
  • 818 views
  • 7 likes
  • 5 in conversation