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.
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.
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.
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.
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;
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.