topic How to get max value from unit in a month in SAS Procedures

How to get max value from unit in a month

BETO — Mon, 20 May 2013 23:31:50 GMT

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

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

PGStats — Tue, 21 May 2013 00:13:44 GMT

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;

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

BETO — Tue, 21 May 2013 03:55:38 GMT

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

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

UrvishShah — Tue, 21 May 2013 06:51:16 GMT

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

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

PGStats — Tue, 21 May 2013 14:34:04 GMT

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;

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

BETO — Fri, 31 May 2013 21:45:44 GMT

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

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

Tom — Fri, 31 May 2013 21:59:41 GMT

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