data test;
input id$2. start end drug ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015 0
1 18FEB2015 30APR2015 1
2 01jan2015 28JAN2015 1
2 01apr2015 30apr2015 1
3 01JAN2015 14FEB2015 1
3 01JAN2015 14FEB2015 0
;
run;
I have the following data. I want to flag the first date, keep the id, drug. If the id have both drug 0 and drug 1 on the same date then delete.
Output data
1 01JAN2015 14FEB2015 0
2 01jan2015 28JAN2015 1
HI @lillymaginta Your sample is all "sets of 2 records for each id", is it really a representative sample. Well, assuming I understand your requirement, here is a SQL solution
data test;
input id$2. start end drug ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015 0
1 18FEB2015 30APR2015 1
2 01jan2015 28JAN2015 1
2 01apr2015 30apr2015 1
3 01JAN2015 14FEB2015 1
3 01JAN2015 14FEB2015 0
;
run;
proc sql;
create table want as
select *
from
(select * from test group by id,start,end having not(count(*)>1 and sum(drug)))
group by id
having min(start)=start;
quit;
HI @lillymaginta Your sample is all "sets of 2 records for each id", is it really a representative sample. Well, assuming I understand your requirement, here is a SQL solution
data test;
input id$2. start end drug ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
1 01JAN2015 14FEB2015 0
1 18FEB2015 30APR2015 1
2 01jan2015 28JAN2015 1
2 01apr2015 30apr2015 1
3 01JAN2015 14FEB2015 1
3 01JAN2015 14FEB2015 0
;
run;
proc sql;
create table want as
select *
from
(select * from test group by id,start,end having not(count(*)>1 and sum(drug)))
group by id
having min(start)=start;
quit;
Thank you!
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.