DATA Step, Macro, Functions and more

keep a specific line

Accepted Solution Solved
Reply
Contributor
Posts: 27
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
‎11-10-2017 10:17 AM
Respected Advisor
Posts: 2,993

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;

 

--
Paige Miller

View solution in original post


All Replies
Solution
‎11-10-2017 10:17 AM
Respected Advisor
Posts: 2,993

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;

 

--
Paige Miller
Super User
Super User
Posts: 9,599

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.

Respected Advisor
Posts: 2,993

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.

--
Paige Miller
Super User
Super User
Posts: 9,599

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
  • 173 views
  • 3 likes
  • 3 in conversation