DATA Step, Macro, Functions and more

finding if an observation happened before a reference date

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 128
Accepted Solution

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 ]
Posted in reply to lillymaginta
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;

View solution in original post


All Replies
Super Contributor
Posts: 490

Re: finding if an observation happened before a reference date

Posted in reply to lillymaginta

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

Frequent Contributor
Posts: 128

Re: finding if an observation happened before a reference date

Posted in reply to mohamed_zaki

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 ]
Posted in reply to lillymaginta
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;
Trusted Advisor
Posts: 1,228

Re: finding if an observation happened before a reference date

Posted in reply to lillymaginta

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: 128

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 230 views
  • 2 likes
  • 3 in conversation