topic Re: flag first date in SAS Programming

flag first date

jojozheng — Wed, 19 Sep 2018 20:39:42 GMT

I want to find first opendate.

I have dataset below(not bold), I want result (bold)to add new column firstdate: same date with earlier opendate marked as 1, same date with same opendate by different item marked as 1.

date opendate id item firstdate

31may2003 o4JAN1998 7 a -------1

31may2003 20may2003 7 a

30jun2007 18jun2007 8 a-------- 1

30jun2007 18jun2007 8 b---------1

Anyone knows how to do this? Thank you so much for your time!

Re: flag first date

novinosrin — Wed, 19 Sep 2018 21:00:19 GMT

data have;
 input   ( date                        opendate ) (:date9.)     id   item $      ;
 format date opendate date9.;
cards;
31may2003              04JAN1998    7     a
31may2003              20may2003    7     a
30jun2007                18jun2007     8    a   
30jun2007                18jun2007     8   b
; 

proc sql;
create table want as
select *,ifn(min(opendate)=opendate,1,.) as firstdate
from have
group by date
order by date, opendate;
quit;

Re: flag first date

jojozheng — Wed, 19 Sep 2018 21:23:42 GMT

thank you for your answer! but i tried it failed. it keeps two records.
31may2003 04JAN1998 7 a
31may2003 20may2003 7 a
while i only want
31may2003 04JAN1998 7 a

Re: flag first date

novinosrin — Wed, 19 Sep 2018 21:30:18 GMT

data have;
 input   ( date                        opendate ) (:date9.)     id   item $      ;
 format date opendate date9.;
cards;
31may2003              04JAN1998    7     a
31may2003              20may2003    7     a
30jun2007                18jun2007     8    a   
30jun2007                18jun2007     8   b
; 

proc sql;
create table want as
select *,ifn(min(opendate)=opendate,1,.) as firstdate
from have
group by date,item
having firstdate=1
order by date, opendate;
quit;

Re: flag first date

novinosrin — Wed, 19 Sep 2018 21:34:15 GMT

or perhaps

group by id,date

Since I do not know your data

Re: flag first date

jojozheng — Wed, 19 Sep 2018 21:41:37 GMT

after add having firstdate=1, it seems like dataset not complete..confused...

Re: flag first date

novinosrin — Wed, 19 Sep 2018 21:44:49 GMT

This is my test and result:

data have;
 input   ( date                        opendate ) (:date9.)     id   item $      ;
 format date opendate date9.;
cards;
31may2003              04JAN1998    7     a
31may2003              20may2003    7     a
30jun2007                18jun2007     8    a   
30jun2007                18jun2007     8   b
; 

proc sql;
create table want as
select *,ifn(min(opendate)=opendate,1,.) as firstdate
from have
group by date,item
having firstdate=1
order by date, opendate;
quit;


proc print noobs;run;

RESULTS:

SAS Output

The SAS System

date	opendate	id	item	firstdate
31MAY2003	04JAN1998	7	a	1
30JUN2007	18JUN2007	8	b	1
30JUN2007	18JUN2007	8	a	1

Re: flag first date

jojozheng — Wed, 19 Sep 2018 21:45:51 GMT

proc sql;
create table want as
select *,ifn(min(opendate)=opendate,1,.) as firstdate
from have
group by id, date
having firstdate=1
order by date, opendate;
quit;

This is correct! Thank you! I really appreciate!

Re: flag first date

jojozheng — Wed, 19 Sep 2018 21:48:17 GMT

Thank you for your answer! Can I ask what's the meaning of this?
ifn(min(opendate)=opendate,1,.)

Re: flag first date

novinosrin — Wed, 19 Sep 2018 21:51:12 GMT

compute the minimum of the opendate and check if any of the opendate is equal to the minimum within the by group(group by), if it is , then assign 1 else assign .