BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
madix
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

 

/* 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

4 REPLIES 4
PaigeMiller
Diamond | Level 26

 

/* 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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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