Max function

[Aar684]

Hi all,

Is there a way to take the MAX of a single variable? If not, any suggestions for finding a maximum value in a column(variable)?

[Flip]

Proc sql;
select max(var) from ...

[Aar684]

I need some serious help. Is there any way I can post an example of a data set to ask questions about?

[Aar684]

So, I'll do my best to explain.

I have a .csv dataset which I import into SAS. This dataset which we'll call usage, contains account numbers and names in a row. Across from the account number and name there is a date ie 01/01/2008 and then there is hour1 - hour24 which contain electricity usage values. Each account number has 366 rows (leap year) one for each day of the year. Each row contains the account number, customer name, date, hour1-hour24. I need to find the max of hour1-hour24 by MONTH. IE there is one peak value in the 744 hours in January. I need to return that value. Then move on to February and so on.........There are about 150 customers in this data set which equals about 55000 rows. Help PLEASE!

[Flip]

Something kind of like this should do it.

data usagesum;
set usage end = eof;
retain maxuse ;

if (day(date) = 1 or eof) and not (_n_ = 1) then do;
month = month(lag(date));
output;
maxuse = 0;
end;
maxuse = max(maxuse , max(h1 - h24));
run;

[Aar684]

Very confusing. Can you clarify a little bit? Keep in mind my SAS knowledge isn't nearly what yours is.

[Aar684]

Actually, I think I have that part. Thank you very much.

How would you recommend I output the account number, month, and maxuse value into something like an Excel file?

[Flip]

data usagesum;
set usage end = eof;
retain maxuse ; * keep the maxuse from obs to obs;
by cust date;
if (day(date) = 1 or last.cust) and not (_n_ = 1) then do; * Do this stuf on the 1st day of month or the last reord for a customer;
month = month(lag(date)); * since we do this on the first of the next month use last month Well you won't want to do this on the last customer record;
output;
maxuse = 0;
end;
maxuse = max(maxuse , max(h1 - h24)); *maximum of retained value and the max of the hourly readings;
run;


This is just a starting point for you, but you should be able to build it from there.

[Aar684]

Ok,

Now I have to add on another part. I need to find the peak usage values for every month like before, but this time I need to find one peak for the hours of 12 pm to 8 pm and I need to find another peak for all other hours not in between 12 and 8. This is still a monthly peak it just needs to be broken up by hours now. Any suggestions?

[deleted_user]

I don't know about the others but personally I learned what I know by sitting back, thinking about things, reading books, trying different things and asking questions when I was really stuck.

I honestly don't believe you cannot figure this answer out for yourself.

I don't mean to be harsh but I'm just saying that getting all the answers form someone else is not going to teach you very much.

If you really really can't figure it out then come back and I can point it out.

By no means do I mean don't ask questions but if the answers are already there and you just need to apply a little bit of work then you're not going to get much out of it by asking for every solution.

[Aar684]

If I new the solution I wouldn't be asking............

[Aar684]

Nor would I be asking if I didn't have to have something done in a short period of time which sort of hampers the whole learning thing.

[deleted_user]

Fair enough, try this.......

data usagesum;
set usage end = eof;
retain maxuse maxuse12_8 maxuse_other ; * keep the maxuse from obs to obs;
by cust date;
if (day(date) = 1 or last.cust) and not (_n_ = 1) then do; * Do this stuf on the 1st day of month or the last reord for a customer;
month = month(lag(date)); * since we do this on the first of the next month use last month Well you won't want to do this on the last customer record;
output;
maxuse = 0;
maxuse12_8=0;
maxuse_other=0;
end;
maxuse = max(maxuse , max(h1 - h24)); *maximum of retained value and the max of the hourly readings;
maxuse12_8 = max(maxuse12_8 , max(h12 - h20)); *maximum of retained value and the max of the hourly readings;
maxuse_other = max(maxuse_other , max(max(h1 - h11),max(h21-h24)); *maximum of retained value and the max of the hourly readings;
run;

Message was edited by: pznew Message was edited by: pznew

[deleted_user]

If you're going to be doing this sort of stuff regularly, have a look at proc means.

*build a test dataset with random usage amounts;
data usage;
*format the date;
format date date9.;
*create 50 accounts;
do i=1 to 50;
account=i;
*366 days in the year;
do x=1 to 366;
date='31DEC2007'd + x;
array h(24);
*24 hours of random usage variables;
do s=1 to 24;
h(s)=round(ranuni(today())*100,0.01);
end;
*output and observation for each date;
output;
end;
end;
run;

*set up the data for the proc means;
data months;
set usage;
*create the maximums for each day;
max_24=max(of h1-h24);
max_12_8=max(of h12-h20);
max_other=max(max(of h1-h11),max(of h21-h24));
*create a variable holding the month;
month=month(date);
run;

*proc means;
proc means data=months noprint nway;
by account month;
var max_24 max_12_8 max_other;
output out=max_usage(drop=_type_ _freq_) max=/autoname;
run;

[sbb]

I tend to agree with feedback posted - it would be more reasonable to see a post with a SAS coding problem, given a particular data-manipulation, calculation or info-output challenge, considering that time is part of the SAS application development process (to include time set aside for learning and as much self-initiated debugging). To continue on one thread topic with multiple questions, after engaging someone's response, detracts from the purpose of the original post.

Scott Barry
SBBWorks, Inc.