Creating a counter that resets at a new subject ID

[_maldini_]

I am trying to create a counter variable, two actually, that counts the number of accomplishments in year 1 and year 2 per subject ID (FB_ID). I want the counter to reset when it moves to a new subject ID. The ultimate goal is to be able to look at the number of accomplishments in year 1 and year 2 for each subject ID.

The dataset (attached) contains the randomization date and the date of the accomplishment, among other things. I am calculating the difference between the two, and then converting to weeks, in order to produce a variable that contains the number of weeks since randomization that the accomplishment occurred (i.e. ACCOMP_WKS).

I am then separating into year 1 and 2 and using a SUM statement to create the counter variables (i.e. ACCOMP_COUNT_YR1, ACCOMP_COUNT_YR2). 

This is just my attempt to get into the correct neighborhood. I have never created a counter before. 

Here is my initial approach. It resets correctly, but the counts don't seem to be correct. Instead of 1,2,3, etc. I see 1,9,2, for example.

Thanks for your help!

DATA want;
 SET have;

 ACCOMP_WKS = YRDIF(Part_RDATE, datepart(BH_Accomplished_Date), "Actual")*52;
 * Creating a variable - ACCOMP_WKS - that equals the number of years between RDATE and BH_Accomplished_Date;
 * BH_Accomplished_Date contains date and time. Using the DATEPART function to exclude the time, so that the YRDIF function can work;
 * Converting to weeks by multiplying by 52;
 
 IF ACCOMP_WKS le 52 THEN ACCOMP_COUNT_YR1 + 1;
 BY FB_ID;
 IF first.FB_ID THEN ACCOMP_COUNT_YR1=1;
 
 IF ACCOMP_WKS gt 52 THEN ACCOMP_COUNT_YR2 + 1; 
 IF first.FB_ID THEN ACCOMP_COUNT_YR2=1; 
 RUN; 

Note. The code above was changed at 11:00am PST.

[Reeza]

Consider using intck with weeks to get interval instead. 

Create a a year variable and add that to your by condition. 

Try getting a counter to work without weeks condition first. 

[Astounding · Accepted solution]

Is PART_RDATE the same for all observations that have the same FB_ID?

At any rate, you may need week calculations for some other purpose.  But for measuring first year vs. second year, you can skip most of the calculations.  For example, if you are willing to approximate a year as being 365 days and ignore leap years:

days_passed = datepart(BH_Accomplished_date) - Part_RDate;

if (0 < days_passed <= 365) then account_comp_yr1 + 1;

else if (366 <= days_passed <= 730) then account_comp_yr2 + 1;

There are details to take care of, like resetting your counts when you begin a new FB_ID.  But you don't need to convert to weeks or years in order to increment your counters.

[PGStats]

Use INTCK and summarize with SQL:

proc sql;
select 
    FB_ID, 
    sum(intck("Year",part_RDATE, BH_Accomplished_Date, "CONTINUOUS") = 0) 
        as ACCOMP_COUNT_YR1,
    sum(intck("Year",part_RDATE, BH_Accomplished_Date, "CONTINUOUS") = 1) 
        as ACCOMP_COUNT_YR2
from have
group by FB_ID;
quit;