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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.