DATA Step, Macro, Functions and more

keep a specific line

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

keep a specific line

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 :

test.PNG

 Thank you for your help


Accepted Solutions
Solution
2 weeks ago
Trusted Advisor
Posts: 1,931

Re: keep a specific line

 

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

 

View solution in original post


All Replies
Solution
2 weeks ago
Trusted Advisor
Posts: 1,931

Re: keep a specific line

 

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

 

Super User
Super User
Posts: 7,981

Re: keep a specific line

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.

Trusted Advisor
Posts: 1,931

Re: keep a specific line

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

Super User
Super User
Posts: 7,981

Re: keep a specific line

Posted in reply to PaigeMiller

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.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 110 views
  • 3 likes
  • 3 in conversation