recode just the multiples of 4 of a variable

[tomas-andriotti]

Hi!

I am trying to recode a variable defined as freq_user in a long data set (longitudinal form). Frequent users (freq_users) were identified as those who went to the emergent department (ED) more often than 4 times a year. It turns out that all observations in a year (visits to ED) were identified as freq_users if the individual has gone more than 4 times to ED - the 1st, 2nd and 3rd as well as the 4th visit. However, I just need to identify as freq_users those observations at the 4th visit or its multiples (8th, 12th and so on) if they were considered so.

Look forward to hearing from you.

Tomas

[Astounding]

Some key questions:

What are the names of your variables?

Does your data set contain a variable holding the YEAR?

Do you trust the flags that were already set up?  For example, if a person has 5 ER visits one year, and 2 the next, are you certain that the 2 visits in the next year are not being flagged?

[tomas-andriotti]

Ansewers to your questions

1. the name of the variables are :

Id: individuals

k: observations or visits

begdate : date of the observations - visit to the ED

fy : fiscal year (sep year X - august year X+1)

freq_user_fy : 4 visits or more to the ED a year

2. yes, the dataset contains fy = fiscal year that was used to code freq users within a fiscal year.

3. I am certain that if the next 2 visits happen in another fiscal year that they were not flagged but if they happened within the fiscal year, all of them were flagged, which is the issue I am trying to solve because I need just the 4th visit to ED within the same fiscal year

thanks 

[Astounding]

OK, here's an approach you can use then:

proc sort data=have;
   by id fy begdate;
run;

data want;
   set have;
   by id fy;
   if first.fy then counter = 1;
   else counter + 1;
   drop counter;
   new_freq_user_fy = (mod(counter, 4) = 0);
run;

This actually counts within the fiscal year.  If you want to count within the calendar year, it's just mildly more complex.  It would require creating a YEAR variable based on the BEGDATE and using that YEAR variable instead of FY.

[tomas-andriotti]

thank you !this worked!
so if I want to count within a calendar year, does the code below just changing fy to year (after creating the calendar year ) suffice?  

proc sort data=have;
   by id year begdate;
run;

data want;
   set have;
   by id year;
   if first.year then counter = 1;
   else counter + 1;
   drop counter;
   new_freq_user_y = (mod(counter, 4) = 0);
run; 

[Astounding · Accepted solution]

Yes, given that you first create YEAR holding the calendar year, that's exactly the right follow-up step.

[tomas-andriotti]

I have a follow-up question regarding this topic - if I want to find the frequent user counting those with 4 visits at the ED in 365 days after each visit instead of 4 visits in a calendar year only, how should I do code it?

[Astounding]

That would require a different approach.  PROC SQL would be a good tool for the job, but is a topic where my skills are just average.  If you really need that, I suggest you post it as a separate question.