topic keep a specific line in SAS Programming

keep a specific line

madix — Fri, 10 Nov 2017 14:26:10 GMT

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

Re: keep a specific line

PaigeMiller — Fri, 10 Nov 2017 14:54:48 GMT

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

Re: keep a specific line

RW9 — Fri, 10 Nov 2017 14:58:50 GMT

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.

Re: keep a specific line

PaigeMiller — Fri, 10 Nov 2017 15:13:43 GMT

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

Re: keep a specific line

RW9 — Fri, 10 Nov 2017 15:25:48 GMT

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.