Indicate whether a date is < 6 months before another date for the same ID column in SQL

Question

I have something like this:

Id     Visit_Date        Visit_code      
A30     15May2004         Surgery            
A30     05FEB2005         Office             
B01     07DEC2002         Office             
B01     21NOV2002         Surgery           

And I need this, with a column indicating whether the person had a surgery within 6 months of their office visit

ID     Office_Visit    SX_past_6mo
A30        1              0
B01        1              1

I am trying things like this but having trouble with the sub query

proc sql;
select case when 
case when
visit_type="Office" then 1 else 0 end as office_visit,
case when 
visit_type="Office" and
DATEDIFF(MM, visit_date, select(visit_date where visit_type="Surgery") > 6 then 1 else 0 end as sx_past_6mo
group by Id

Answer 1

You need to join the table to itself. This code gives you each Surgery and the (First Office Visit After that Surgery), and (First Office Visit Months After that Surgery). I am not sure if you wanted the office visit before or after the surgery, but I went with after.

select pas.Id as ID, pas.Visit_Date as SurgeryDate, Min(pao.Visit_Date) as FirstOfficeDateAfterSurgery, DATEDIFF(MM, pas.Visit_Date,Min(pao.Visit_Date)) as FirstOfficeMonthsAfterSurgery,
case 
    when isnull(Min(pao.Visit_Date),'01/01/1900') = '01/01/1900' then 0 
    when DATEDIFF(MM, pas.Visit_Date,Min(pao.Visit_Date)) <= 6 then 1 
    else 0 end as SX_past_6mo

from PatientActivity pas
left join PatientActivity pao 
    on pas.Id = pao.Id 
        and pao.Visit_code = 'Office' 
        and pao.Visit_Date > pas.Visit_Date
where pas.Visit_code = 'Surgery'
group by pas.Id, pas.Visit_Date