How to get max value from unit in a month

[BETO]

Hi need assistance with a table that was imported into SAS the table has 3 columns

unit 1.  $100 05/01/12

unit 2.  $59.   05/12/12

unit 2.   33.   05/13/12

unit 3.   $ 60.  05 /12/12

unit 3.  55.   05/20/12

unit 1 $25 05/05/12

what I need is to Id only the max $ value by date

THis is what I'm looking for

unit 1 $100 05/01/12

unit 2 $59 05/12/12

unit 3 $60 05/12/12

what is coming up is max value for each day which brings everything in...thank you for you assistance

[PGStats]

Try this :

data have;
input @5 unit value :comma5. date : mmddyy8.;
format date date9.;
datalines;
unit 1.  $100 05/01/12
unit 2.  $59.   05/12/12
unit 2.   33.   05/13/12
unit 3.   $60.  05/12/12
unit 3.  55.   05/20/12
unit 1 $25  05/05/12
;

proc sql;
create table want as
select
     unit,
     intnx("MONTH",date,0) as month format = yymmd7.,
     value
from have
group by
     unit,
     calculated month
having value=max(value);
select * from want;
quit;

PG

[BETO]

HI PGStats,

thanks you  for responding to my question. It's real close you script did bring out the max value and the unit # the only thing that is missing is the  date  of when the max value occurred . what appear is the month 05/2012. I need the date brought over with it  when it hit max value...thanks again for assistance

[PGStats · Accepted solution]

Simply add the date to the list of fields that you want :

proc sql;

create table maxValue as

select

     unit,

     date,

     intnx("MONTH",date,0) as month format = yymmd7.,

     value

from have

group by

     unit,

     calculated month

having value=max(value);

select * from maxValue;

quit;

PG

[BETO]

HI PGStats,

thank you for help.  I must be doing something wrong because when I run the script it doesn't give me the highest usage $ per day of the month select. It gives me lowest Amt. what do you think is causing that that? Thanks again for all your help

[Tom]

Are you sure VALUE is a numeric variable?  If it is character then it will take the max when sorting alphabetically.  So '9' would be "greater" then '1000'.

[UrvishShah]

Hi,

What about this ???

proc sort data = have;

  by unit value;

run;

data want;

  set have;

  by unit value

  if last.unit and last.value then output;

run;

Hope it meets your requirement...

-Urvish