Counting events over time

Reply
New Contributor
Posts: 2

Counting events over time

[ Edited ]

I have a different start date (i.e., baseline date) for 300 or so subjects. From each subject's baseline date I want to count back, in 30-day increments, for 24 periods and sum the number of treatment encounters in each 30-day period. Then I want to count forward, from the baseline date, again in 30-day increments, for another 24 periods—essentially two years pre and post. (In addition to treatment encounters, I also have to do the same thing for a number of other count variables.)

 

So here is my WANT dataset for six pre- and post-periods for two subjects and their number of outpatient treatment encounters:

 

IDtx_pre_19tx_pre_20tx_pre_21tx_pre_22tx_pre_23tx_pre_24baselinetx_post_1tx_post_2tx_post_3tx_post_4tx_post_5tx_post_6
14280001-Oct-09269191410
200002210-Oct-09875589

 

And here is my HAVE dataset for ID 1:

IDOP_txAssessBaselineService_date
11 1-Oct-094-Apr-09
1 11-Oct-099-Apr-09
11 1-Oct-0915-Apr-09
11 1-Oct-0915-Apr-09
11 1-Oct-093-May-09
11 1-Oct-094-May-09
11 1-Oct-0925-May-09

 

The HAVE displays the four outpatient service encounters that are listed in tx_pre_19. That is, in the 19th 30-day period before baseline, ID 1 had 4 outpatient treatment encounters. Those four encounters are present between 4-APR-09 and 3-MAY-09 (note that it is possible for someone to have more than 1 encounter on a given day—15-APR-09). In the 20th period before baseline there are 2 outpatient treatment encounters for ID 1 (in WANT); those two encounters took place on 4-MAY-09 and 25-MAY-09 (in HAVE).

 

It’s the floating baseline date for each subject that has stumped me. I’ve done something similar to this, but everyone had the same start (baseline) date, so I could use an array…something like this:

 

data OP2;

set OP1;

drop _type_ _freq_ _stat_;

array Psych [24] x1count_psy--x88count_psy;

do month = 1 to 24;

Psych_VST = Psych[month];

output;

end;

drop x1count_psy--x24count_psy;

run;

 

But with the different start dates in the current data, the ‘month’ or 30-day period, as defined in the above array, isn’t the same for everyone (cf., ID 1 starting on OCT 1 and ID 2 starting on OCT 10). I imagine this would be relatively easy to do in SQL, but I've never learned or coded in that approach.

 

Thank you for any feedback/tips.

Richard

Grand Advisor
Posts: 9,567

Re: Counting events over time

Yes. It is easy for SQL.

 

data have;
infile cards expandtabs truncover;
input ID	Assess	(Baseline	Service_date) (: date9.);
format Baseline	Service_date date9.;
cards;
1	1	 	1-Oct-09	4-Apr-09
1	 	1	1-Oct-09	9-Apr-09
1	1	 	1-Oct-09	15-Apr-09
1	1	 	1-Oct-09	1-Nov-09
1	1	 	1-Oct-09	3-Oct-09
1	1	 	1-Oct-09	4-Oct-09
1	1	 	1-Oct-09	25-Sep-09
;
run;
proc sql;
select a.id,a.baseline,
(select count(*) from have 
where id=a.id and Service_date between intnx('day',a.baseline,-30) and a.baseline) as pre_1,
(select count(*) from have 
where id=a.id and Service_date between  a.baseline and intnx('day',a.baseline,30) ) as post_1

 from (select distinct id,baseline from have) as a;
quit;
New Contributor
Posts: 2

Re: Counting events over time

Thank you for this, Ksharp! Your solution worked perfectly for month 1. However, as I noted in my original post, I am quite useless with SQL, and despite my attempts, I haven't been able to figure out how to modify your code for the remaining 23 months pre- and post-baseline. Are you able to show me a quick example of how you would also code pre_2 and post_2 in your scheme?

 

Thanks again!

Grand Advisor
Posts: 9,567

Re: Counting events over time

Just count it as 30 days as a unit.

 

proc sql;
select a.id,a.baseline,

(select count(*) from have 
where id=a.id and Service_date between intnx('day',a.baseline,-30) and a.baseline) as pre_1,


(select count(*) from have 
where id=a.id and Service_date between intnx('day',a.baseline,-30*2) and a.baseline) as pre_2,


(select count(*) from have 
where id=a.id and Service_date between intnx('day',a.baseline,-30*3) and a.baseline) as pre_3,




(select count(*) from have 
where id=a.id and Service_date between  a.baseline and intnx('day',a.baseline,30) ) as post_1


(select count(*) from have 
where id=a.id and Service_date between  a.baseline and intnx('day',a.baseline,30*2) ) as post_2


(select count(*) from have 
where id=a.id and Service_date between  a.baseline and intnx('day',a.baseline,30*3) ) as post_3



 from (select distinct id,baseline from have) as a;
quit;
Ask a Question
Discussion stats
  • 3 replies
  • 275 views
  • 0 likes
  • 2 in conversation