BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jojozheng
Quartz | Level 8

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

or perhaps

 

group by id,date

 Since I do not know your data

View solution in original post

9 REPLIES 9
novinosrin
Tourmaline | Level 20
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
Quartz | Level 8
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
Tourmaline | Level 20
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
Tourmaline | Level 20

or perhaps

 

group by id,date

 Since I do not know your data

jojozheng
Quartz | Level 8
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
Quartz | Level 8
after add having firstdate=1, it seems like dataset not complete..confused...
novinosrin
Tourmaline | Level 20

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
Quartz | Level 8
Thank you for your answer! Can I ask what's the meaning of this?
ifn(min(opendate)=opendate,1,.)
novinosrin
Tourmaline | Level 20

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 .

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 3556 views
  • 5 likes
  • 2 in conversation