flag first date

[jojozheng]

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! 

[novinosrin]

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;

[jojozheng]

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

[novinosrin]

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;

[novinosrin · Accepted solution]

or perhaps

group by id,date

 Since I do not know your data

[jojozheng]

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!

[jojozheng]

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

[novinosrin]

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

[jojozheng]

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

[novinosrin]

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 .