Rolling count of IDs over time frame with condition

I have a data set like the following:

 data work.have;
    input ID mydate date9. type;
    format mydate date9.;
    datalines;
1 11NOV2020 1
1 22OCT2020 1
1 24SEP2020 0
1 26SEP2019 1
2 13OCT2021 1
2 11SEP2020 1
;
run;

• I want to count the amount of times an ID has appeared in the last 12 months starting from the mydate variable, by ID.
• The count should start at 0.
• It should only increase the count by 1 if there are any observations in the last 12 months, and those observations have type = 1

The result I want should look something like this:

ID    mydate        type    Count
1     11NOV2020     1       1
1     22OCT2020     1       0
1     24SEP2020     0       1
1     26SEP2019     1       0
2     13OCT2021     1       0
2     11SEP2020     1       0

For example ID = 1 on the first row gets a count = 1 because it only has one observation the last 12 months where type = 1.

So far I have tried merging the table on itself like the below but it does not produce the correct result.

proc sql;
    create table work.want
        as select
         t1.ID
        ,t1.mydate
        ,max(count(t2.id)-1,0) as Count
            from work.have as t1
            left join
            work.have as t2
            on t1.id = t2.id and 0 <= intck('month', t2.mydate, t1.mydate) <= 12 and t2.Type = 1
                group by 1,2
                    order by 1,2 desc;
Quit;

I would gladly appreciate an answer in either proc sql or as a data step.

Thank you in advance!

Solution 1:

Try this

 data work.have;
    input ID mydate date9. type;
    format mydate date9.;
    datalines;
1 11NOV2020 1
1 22OCT2020 1
1 24SEP2020 0
1 26SEP2019 1
2 13OCT2021 1
2 11SEP2020 1
;
run;

proc sql;
   create table want as
   select *, 
           (select n(type) from have
               where id = a.id
                 and intnx('year', a.mydate, -1, 's') <= mydate < a.mydate
                 and type = 1
           ) as count
   from have as a;
quit;