## How to get max value from unit in a month

Solved
Regular Contributor
Posts: 240

# How to get max value from unit in a month

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

Accepted Solutions
Solution
‎05-21-2013 10:34 AM
Posts: 5,529

## Re: How to get max value from unit in a month

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

PG

All Replies
Posts: 5,529

## Re: How to get max value from unit in a month

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

PG
Regular Contributor
Posts: 240

## Re: How to get max value from unit in a month

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

Solution
‎05-21-2013 10:34 AM
Posts: 5,529

## Re: How to get max value from unit in a month

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

PG
Regular Contributor
Posts: 240

## Re: How to get max value from unit in a month

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

Super User
Posts: 8,106

## Re: How to get max value from unit in a month

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'.

Regular Contributor
Posts: 195

## Re: How to get max value from unit in a month

Hi,

proc sort data = have;

by unit value;

run;

data want;

set have;

by unit value

if last.unit and last.value then output;

run;