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
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;
Is there will always be only one hf=1 per id?
yes one hf per id
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.