CODEWITHSUNDEEP
sqlsas
M Darblade
M Darblade

Reputation: 333

Updating table on condition with sas proc sql

I have a table like this

ID         date1         date2
 1    01/01/2007    31/12/2007
 1    01/01/2008    31/12/2008
 2    01/01/2007    31/12/2007
 ...

What I want to do is to sort by ID the table without dupkey on ID, and when there is more than one entry for the ID, keep the minimum between the date1 of every similar ID and the maximum of every similar ID for date2.

The code I tried for the first date is the following :

proc sql;
    update table
    set Date1 = (SELECT b.dateAffiliation
                 from table b
                 where b.date1 > Date1
                 and B.ID=ID)
quit;

Unfortunatly, it seems that I can't do that with SAS.

I got the same problem using this code :

proc sql;
  update table
  set a.Date1 = b.Date1
  from table as a join table as b
      on a.Date1 > b.Date1
      And a.ID=b.ID;
quit;

Upvotes: 1

Views: 287

Answers (1)

M Darblade
M Darblade

Reputation: 333

I got the solution :

proc sql;
    create table wanted as
    select *, min(Date1) as date1min, max(date2) as date2max
    from have
    group by ID;
quit;

Upvotes: 1

Related Questions