Hello Fellow SAS Users,
I have been working on something for a few weeks and just ran into a problem with my code. Esentially, I need SAS to count the number of consecutive variable values (including restarting values) separately for each participant.
(I am not going to include my current SAS code, because I am sure it is very inefficient and there is a much better way to achieve this.)
I have two problems, but first take a look at an example of the output:
NAME = Participant
DSCD = Identifier
FYEAR = Year of observation
AUD = the variable for which I need to count the number of repititions
TENURE = the count of the repition of AUD, as of the current FYEAR
My two problems are summarized as follows (based no the two red boxes😞
1. First, the very first observation for each participant should be where TENURE=1. My code is showing as TENURE=. and then starting TENURE=1 in the second observation.
2. The more important problem is what happens in the second participant. The same problem as #1 above persists. But then in FYEAR=1996 for this participant, it is continuing off of the previous count string.
Yet, interestingly, for both participants shown here (look at the BLUE boxes, FYEAR=2002 for the first and FYEAR=2007 for the second), when there is a change in the AUD variable, neither of the two preceding problems are present. The first year of the new AUD variable is = 1, and it restarts the count rather than pulling from the previous count.
I would appreciate any help with this. Again, I'm sorry for not including my current SAS code but I am very confident that it is wasteful and inefficient.
Update: I am trying to create a simple DO loop and this is also giving me problems. Here's the code I'm working with for that:
*COMPUTE TENURE (IN YEARS) AND CLASSIFY AS S/M/L*; data tenure; set merged; aud=aud2; dscd1 = lag(dscd); aud1 = lag(aud); if dscd ne dscd1 then tenure=1; else if dscd=dscd1 then do; tenure1=lag(tenure); if aud=aud1 then tenure = tenure1+1; if aud ne aud1 then tenure = 1; end; if tenure <=3 then tenure_c='S'; else if 7 >= tenure > 3 then tenure_c='M'; else if tenure > 7 then tenure_c='L'; else if tenure='.' then tenure_c='.'; run;
I realize this post is 3 years-old (and the original poster had already resolved the issue him/herself), but I have found myself in a similar situation, stumbling across a SAS community question that perfectly replicates the question I'm trying to sort out, with no final resolution. Just in case others are struggling to find syntax to create a count variable, here is some code to help:
First, sort data by your grouping variable and the variable you want counted. ID = grouping variable; Date = ordering variable
proc sort data=one; by ID Date; run;
Next, create a count variable in a new data set. In this case, each first ID starts at a count of 1, and continues to count up until the next ID.
data two; set one; *Create count variable.; Count + 1; by ID; if first.ID then Count = 1; run;
If you wanted to create a running count within more than one category, for example, number of observations within each participant and date, you can simply add the additional variable to your count syntax after the "by" statement (e.g., "by ID Date").
If you need something slightly more complicated, for example, counting only certain types of observations, some additional syntax is listed below.
data two; set one; *Create drinking observation count.; if Drinking=1 then Count + 1; by ID; if first.ID then Count = 1; run;
Here, I created a count of drinking observations (only when drinking = 1, not drinking = 0) within each participant ID.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.