I sorted by id and date (mm/dd/yy).
I need to create a running count by id and job (while keeping the dates in order).
How do I create the variable count?
id date job count
1 1/1/10 x 1
1 1/15/10 y 2
1 2/2/10 y 2
1 4/1/10 x 3
1 5/1/10 x 3
1 4/2/11 z 4
2 4/3/09 x 1
2 4/6/09 m 2
2 5/8/09 m 2
2 6/8/10 m 2
2 7/8/10 y 3
2 7/8/11 m 4
2 8/8/11 m 4
If that obs were 4,then it is easy.
data temp; input id date : mmddyy10. job $; format date mmddyy10.; cards; 1 1/1/10 x 1 1/15/10 y 1 2/2/10 y 1 4/1/10 x 1 5/1/10 x 1 4/2/11 z 2 4/3/09 x 2 4/6/09 m 2 5/8/09 m 2 6/8/10 m 2 7/8/10 y 2 7/8/11 m ; run; data want; set temp; if id ne lag(id) then count=0; if job ne lag(job) then count+1; run;
Ksharp
Message was edited by: xia keshan
id date job count
1 1/1/10 x 1
1 1/15/10 y 2
1 2/2/10 y 2
1 4/1/10 x 3
1 5/1/10 x 3
1 4/2/11 z 1
2 4/3/09 x 1
2 4/6/09 m 2
2 5/8/09 m 2
2 6/8/10 m 2
2 7/8/10 y 3
2 7/8/11 m 4
2 8/8/11 m 4
Why this obs 's count is 1 not 4? There are some other logic?
Ksharp
If that obs were 4,then it is easy.
data temp; input id date : mmddyy10. job $; format date mmddyy10.; cards; 1 1/1/10 x 1 1/15/10 y 1 2/2/10 y 1 4/1/10 x 1 5/1/10 x 1 4/2/11 z 2 4/3/09 x 2 4/6/09 m 2 5/8/09 m 2 6/8/10 m 2 7/8/10 y 2 7/8/11 m ; run; data want; set temp; if id ne lag(id) then count=0; if job ne lag(job) then count+1; run;
Ksharp
Message was edited by: xia keshan
Hi ... try some FIRST. variables ...
* leave C1 in the data set to see if logic worked;
data test;
input id date : mmddyy. job : $1. c1;
format date mmddyy10.;
datalines;
1 1/1/10 x 1
1 1/15/10 y 2
1 2/2/10 y 2
1 4/1/10 x 3
1 5/1/10 x 3
1 4/2/11 z 4
2 4/3/09 x 1
2 4/6/09 m 2
2 5/8/09 m 2
2 6/8/10 m 2
2 7/8/10 y 3
2 7/8/11 m 4
2 8/8/11 m 4
;
run;
* create C2 (should be same as C1);
data test;
set test;
by id job notsorted;
c2+(-c2*first.id);
c2+first.job;
run;
* hey, it is !!! ;
proc print data=test;
run;
id date job c1 c2
1 01/01/2010 x 1 1
1 01/15/2010 y 2 2
1 02/02/2010 y 2 2
1 04/01/2010 x 3 3
1 05/01/2010 x 3 3
1 04/02/2011 z 4 4
2 04/03/2009 x 1 1
2 04/06/2009 m 2 2
2 05/08/2009 m 2 2
2 06/08/2010 m 2 2
2 07/08/2010 y 3 3
2 07/08/2011 m 4 4
2 08/08/2011 m 4 4
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.
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.