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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.