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!
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;
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;
or perhaps
group by id,date
Since I do not know your data
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 |
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 .
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.