How many within a one year period...

[TashaChapWUSS]

I have a dataset with the following variables:

• Inspection ID
• Date
• Employer ID
• Count of violations

There are five years worth of data, one row per inspection, and an employer can have multiple inspections.

The question is: How many times did the same employer receive three or more violations (not necessarily in the same inspection) within a one-year period?

In other words, for every inspection record in the original dataset, I also need to count back to any inspections within the previous year (12 months/365 days) of the date of that original inspection and count the sum total of violations.

Thoughts on how to do this?  SAS code or Proc SQL are preferred, but I'll work with anything you can come up with...  I was thinking maybe a correlated subquery, but my initial attempt at the code didn't work and I kinda broke my brain. 🙂

[ballardw]

If might help to show us your attempt at the subquery.

At a minimum it will show variable names and data set name. It might be that your subquery was just in the wrong place or a condition was not quite matching your description.

For one thing were you using the INTCK or INTNX functions for interval determination?

[TashaChapWUSS]

Here's my initial attempt.  I've worked with subqueries a lot, but not correlated subqueries before, so I'm stumped.

proc sql;
create table empcount as 
select c.inspection_no,
		c.open_date,
		c.employer_no,
        c.v_count,
		y.v_past
from cleanvios as c left join 
		(select employer_no, sum(v_count) as w_past
			from cleanvios as p
		where c.employer_no = p.employer_no and intnx('YEAR', c.open_date, 1, 'SAME') le p.open_date lt c.open_date
		group by employer_no) as y
		on c.employer_no = y.employer_no;
quit;

[TashaChapWUSS]

From there I was going to add the V_Count and V_Past together. But basically I just don't know how to do that look-back properly.

[s_lassen]

You can just make a subquery to get the one calculated column:

proc sql;
create table empcount as 
select c.inspection_no,
		c.open_date,
		c.employer_no,
        c.v_count,
		(select sum(v_count) from cleanvios as p
		  where p.employer_no=c.employer_no
		    and intnx('YEAR', c.open_date, 1, 'SAME') le p.open_date lt c.open_date)
		 as v_past
from cleanvios as c;
quit;

(code not tested, as you did not provide any example data)