Hello community, I hope you can help me with my programming. I am looking to define time period for a diagnosis via proc sql. A participant can be registered several times during the follow-up period, but I want them only to appear in the time period if it is the first time they are assigned the diagnosis (variable: in_date = date the diagnosis is assigned). I am working with the following time periods: - before one year after first visit (only participants who is registered with the diagnosis before one year after first visit) - between first and last visit (only participants with the first registration between first and last visit) - after last visit (only participants with the first registration of diagnosis after last visit) - any time point (should include everybody) I have written the following proc sql, but the problem with this code is, that the participant can potentially appear in all of the groups, because he/she is registered several times during the whole follow-up period. How to specify that it has to be the first time, the diagnosis is assigned? proc sql; create table diagnosis as select distinct a.id, b.in_date, b.diagnosis, b.first_visit, b.last_visit, case when substr(b.diagnosis,2,4) in ("XX", "XX", "XX) and .<b.first_visit+365>=b.in_date then 1 else 0 end as diagnosis_1year_after_visit, case when substr(b.diagnosis,2,4) in ("XX", "XX", "XX) and .<b.last_visit>b.in_date then 1 else 0 end as diagnosis_after_last_visit, case when substr(b.diagnosis,2,4) in ("XX", "XX", "XX) and b.first_visit<b.in_date<last_visit then 1 else 0 end as diagnosis_between_visits, case when substr(b.diagnosis,2,4) in ("XX", "XX", "XX) then 1 else 0 end as diagnosis_ever from test1 as b left join id as a on a.id=b.id group by iid order by id;quit
... View more