Help using Base SAS procedures

earlier dates only for each id

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

earlier dates only for each id

Hi all,

I have data in the following format (some has dates missing):

ID             Date 1          Date 2         Date 3

120111/29/200711/1/200711/1/2007
12206/1/2002
12308/1/20028/1/20028/1/2002
12304/1/20004/1/20004/1/2000
12377/1/20037/1/20037/1/2003

I want to do the following:

If one ID has more than one date i's (for i = 1,2,3), I want to keep only the earlier date.

ID             Date 1          Date 2         Date 3

120111/29/200711/1/200711/1/2007
12206/1/2002
12304/1/20004/1/20004/1/2000
12377/1/20037/1/20037/1/2003

How can I do this?

Thanks,

Chris


Accepted Solutions
Solution
‎02-02-2012 10:18 PM
Respected Advisor
Posts: 3,156

Re: earlier dates only for each id

Then it becomes a classic SQL solution:

data have;

infile cards missover;

format date1 mmddyy10.

       date2 mmddyy10.

       date3 mmddyy10.;

input id $ (date1-date3) (: mmddyy10.);

cards;

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002      

1230    8/1/2002    8/1/1998    8/1/2002

1230    4/1/2000    4/1/2000    4/1/1999

1237    7/1/2003    7/1/2003    7/1/1999

;

proc sql;

create table want as

select id, min(date1) as date1 format mmddyy10., min(date2) as date2 format mmddyy10.,

       min(date3) as date3 format mmddyy10. from have

group by id;

quit;

proc print;run;

Regards,

Haikuo

View solution in original post


All Replies
Respected Advisor
Posts: 3,156

earlier dates only for each id

Basically you need to sort your data based on ID and date, then utilize first.variable or last.variable to capture what you want.

data have;

infile cards missover;

format date1 mmddyy10.

       date2 mmddyy10.

       date3 mmddyy10.;

input id $ (date1-date3) (: mmddyy10.);

cards;

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002       

1230    8/1/2002    8/1/2002    8/1/2002

1230    4/1/2000    4/1/2000    4/1/2000

1237    7/1/2003    7/1/2003    7/1/2003

;

proc sort data=have;

by id date1;

run;

data want;

set have;

by id date1;

if first.id;

run;

proc print;run;

Regards,

Haikuo

Contributor
Posts: 44

Re: earlier dates only for each id

I provided a bad example in my question.

it's not always the case that a row with the earliest date1 also has the earliest date 2 and date 3.

for example,

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002      

1230    8/1/2002    "1/1/1998"    8/1/2002

1230    4/1/2000    4/1/2000    4/1/2000

1237    7/1/2003    7/1/2003    7/1/2003

should become:

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002     

1230    4/1/2000    "1/1/1998"    4/1/2000

1237    7/1/2003    7/1/2003    7/1/2003

Contributor
Posts: 44

earlier dates only for each id

please see my response to Hai.kuo's answer. I gave a bad example... and I'm giving a better example there. thanks

Super Contributor
Posts: 1,636

Re: earlier dates only for each id

do you want something like this?

data have;

infile cards missover;

format date1 mmddyy10.

       date2 mmddyy10.

       date3 mmddyy10.;

input id $ (date1-date3) (: mmddyy10.);

cards;

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002      

1230    4/1/2000    1/1/1998    4/1/2000

1237    7/1/2003    7/1/2003    7/1/2003

;

data want(keep=id date);

  set have;

  date=smallest(1,of date1-date3);

  format date mmddyy10.;

  run;

  proc print;run;

                             Obs     id           date

                                1     1201    11/01/2007

                                2     1220    06/01/2002

                                3     1230    01/01/1998

                                4     1237    07/01/2003

Linlin

Contributor
Posts: 44

Re: earlier dates only for each id

no. this is different from the example I posted.

Solution
‎02-02-2012 10:18 PM
Respected Advisor
Posts: 3,156

Re: earlier dates only for each id

Then it becomes a classic SQL solution:

data have;

infile cards missover;

format date1 mmddyy10.

       date2 mmddyy10.

       date3 mmddyy10.;

input id $ (date1-date3) (: mmddyy10.);

cards;

1201    11/29/2007    11/1/2007    11/1/2007

1220    6/1/2002      

1230    8/1/2002    8/1/1998    8/1/2002

1230    4/1/2000    4/1/2000    4/1/1999

1237    7/1/2003    7/1/2003    7/1/1999

;

proc sql;

create table want as

select id, min(date1) as date1 format mmddyy10., min(date2) as date2 format mmddyy10.,

       min(date3) as date3 format mmddyy10. from have

group by id;

quit;

proc print;run;

Regards,

Haikuo

Contributor
Posts: 44

Re: earlier dates only for each id

thanks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 170 views
  • 0 likes
  • 3 in conversation