flag the first date

[lillymaginta]

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

[novinosrin · Accepted solution]

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;

[lillymaginta]

Thank you!