topic Re: flag the first date in SAS Programming

flag the first date

lillymaginta — Sat, 29 Feb 2020 15:39:57 GMT

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

Re: flag the first date

novinosrin — Sat, 29 Feb 2020 15:49:05 GMT

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;

Re: flag the first date

lillymaginta — Sat, 29 Feb 2020 16:02:06 GMT

Thank you!