## finding if an observation happened before a reference date

Solved
Frequent Contributor
Posts: 136

# finding if an observation happened before a reference date

I have the following data, multiple observation per id. several s_Date, one adm_date per id,

I want to do the following: if hf=1 and the s_date is before adm_date then delete all observation for that id.

``````id       s_date           hf      adm_Date
1        1/1/2005       1       2/2/2004
1        7/7/2005       0
2        4/3/2004       0
2       2/2/2005        1       6/7/2005
2                       0
3                       0
3       4/4/2006        1     8/8/2005
3``````

Accepted Solutions
Solution
‎02-13-2016 08:42 PM
Super Contributor
Posts: 490

## Re: finding if an observation happened before a reference date

[ Edited ]
``````data have;
input id s_date ddmmyy9. hf adm_Date ddmmyy9.;
cards;
1 1/1/2005 1 2/2/2004
1 7/7/2005 0 .
2 4/3/2004 0 .
2 2/2/2005 1 6/7/2005
2 . 0 .
3 . 0 .
3 4/4/2006 1 8/8/2005
;
run;

proc sql ;
create table want as
select *
from have
where id not in (select distinct id from have where s_date < adm_date and hf=1)
;
quit;``````

All Replies
Super Contributor
Posts: 490

## Re: finding if an observation happened before a reference date

Is there will always be only one hf=1 per id?

Frequent Contributor
Posts: 136

## Re: finding if an observation happened before a reference date

yes one hf per id

Solution
‎02-13-2016 08:42 PM
Super Contributor
Posts: 490

## Re: finding if an observation happened before a reference date

[ Edited ]
``````data have;
input id s_date ddmmyy9. hf adm_Date ddmmyy9.;
cards;
1 1/1/2005 1 2/2/2004
1 7/7/2005 0 .
2 4/3/2004 0 .
2 2/2/2005 1 6/7/2005
2 . 0 .
3 . 0 .
3 4/4/2006 1 8/8/2005
;
run;

proc sql ;
create table want as
select *
from have
where id not in (select distinct id from have where s_date < adm_date and hf=1)
;
quit;``````
Posts: 1,270

## Re: finding if an observation happened before a reference date

proc sql;
create table want as
select * from have
group by id
having sum(case when s_date<adm_date and hf=1 then 1 else 0 end)=0;
quit;

Frequent Contributor
Posts: 136

## Re: finding if an observation happened before a reference date

Thank you Mohamed asnd stat_sas both codes worked and produced the same results. I appreciate it
🔒 This topic is solved and locked.