keep a specific line

[madix]

Hello,

I have the following problem. I want to keep each line in a month where  b is a maximum. For example, this is what I will keep :

 Thank you for your help

[PaigeMiller · Accepted solution]

/* UNTESTED CODE */
proc sql;
    create table want as select * from have group by mdy(month(f1),1,year(f1)) having b=max(b);
quit;

[RW9]

Something like:

data inter;
  set have;
  mnth=month(f1);
run;
proc sort data=inter out=want;
  by mnth descending b;
run;

data want;
  set want;
  by mnth;
  if first.mnth;
run;

Or you could do it in SQL by

proc sql;
  create table WANT as
  select * from have group by month(f1) having b=max(b);
quit;

Or proc summary, or means.

[PaigeMiller]

@RW9 This works if all the dates are in the same year, doesn't work if the dates span multiple years.

[RW9]

He never mentioned that, if so just change the month() function for a put():

proc sql;
  create table WANT as
  select * from have group by put(f1,mmyy6.) having b=max(b);
quit;

Obviously not tested as don't have time to type his test data in.