topic Re: Max function in SAS Programming

Max function

Aar684 — Thu, 26 Mar 2009 13:37:48 GMT

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)?

Re: Max function

Flip — Thu, 26 Mar 2009 13:40:39 GMT

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

Re: Max function

Aar684 — Thu, 26 Mar 2009 13:46:14 GMT

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

Re: Max function

Aar684 — Thu, 26 Mar 2009 13:52:25 GMT

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!

Re: Max function

Flip — Thu, 26 Mar 2009 14:04:14 GMT

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;

Re: Max function

Aar684 — Thu, 26 Mar 2009 14:11:32 GMT

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

Re: Max function

Aar684 — Thu, 26 Mar 2009 14:28:29 GMT

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?

Re: Max function

Flip — Thu, 26 Mar 2009 14:29:09 GMT

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.

Re: Max function

Aar684 — Thu, 26 Mar 2009 15:19:27 GMT

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?

Re: Max function

deleted_user — Thu, 26 Mar 2009 15:38:38 GMT

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.

Re: Max function

Aar684 — Thu, 26 Mar 2009 15:47:55 GMT

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

Re: Max function

Aar684 — Thu, 26 Mar 2009 15:48:56 GMT

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.

Re: Max function

deleted_user — Thu, 26 Mar 2009 16:12:09 GMT

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

Re: Max function

deleted_user — Thu, 26 Mar 2009 16:50:05 GMT

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;

Re: Max function

sbb — Thu, 26 Mar 2009 16:54:43 GMT

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.

Re: Max function

Aar684 — Fri, 27 Mar 2009 12:52:17 GMT

data kva;
infile 'C:\Documents and Settings\dubauaw\Desktop\Jims KVA 2009\Rate 56 (Group 1)Cal.csv' dsd missover LRECL=1000000;
length account $9. meter $9. channel $12. LRID $15. name $25. meteragain $9.;
input account $ meter $ regtype $ channel $ LRID $ name $ meteragain $ type1 $ type2 $ date hour1-hour24;
informat date mmddyy9.;
run;

data usagesum;
set kva end = eof;
retain maxuse maxuse12_8 maxuse_other max_offpeak ;
if (day(date) = 1 or last.account) and not (_n_ = 1) then do;
month = month(lag(date));
output;
maxuse = 0;
maxuse12_8=0;
maxuse_other=0;
max_offpeak =0;
end;
maxuse = max(maxuse , max(of hour1 - hour24));
maxuse12_8 = max(maxuse12_8 , max(of hour12 - hour20));
maxuse_other = max(of maxuse_other , max(max(of hour1 - hour11),max(of hour21-hour24)));
if weekday(date) NE 2-6 then do;
max_offpeak = max(of maxuse_other, max(of hour1-hour24));
end;
run;

There is my code. My only problem now is the first account is missing a month. The month variable works like it should but the first account skips a month. Not sure why? That is to say that the month variable works all the way through, but something isn't lining up. Message was edited by: Aar684

Re: Max function

deleted_user — Fri, 27 Mar 2009 13:32:57 GMT

What is the month variable for?

Re: Max function

Aar684 — Fri, 27 Mar 2009 13:43:12 GMT

just to keep track of the month I believe.

my problem is the first account only does 11 months of peaks where as the rest of the data set/accounts have all 12, any ideas?

Re: Max function

Flip — Fri, 27 Mar 2009 14:04:18 GMT

if _n_ = 1 then month = month(date);

Re: Max function

deleted_user — Fri, 27 Mar 2009 14:06:45 GMT

Have you heard of the software development lifecycle?

Basically it teaches us that the most important thing is to get the requirements right from the start. This is a case in point. Because you asked for something at the start and then changed your requirements the program is becoming a mess.

If you fix that then other things go wrong and it just takes a whole lot of messing around. I'll try:

data usagesum;
set kva end = eof;
retain maxuse maxuse12_8 maxuse_other max_offpeak;
month = month(date);
*if its the first of the month of the first observation for the account;
*set all the reained varaibles to zero, to start again;
if (day(date) = 1 or first.account) then do;
maxuse = 0;
maxuse12_8=0;
maxuse_other=0;
max_offpeak =0;
end;
maxuse = max(maxuse , max(of hour1 - hour24));
maxuse12_8 = max(maxuse12_8 , max(of hour12 - hour20));
maxuse_other = max(of maxuse_other , max(max(of hour1 - hour11),max(of hour21-hour24)));
if weekday(date) NE 2-6 then do;
max_offpeak = max(of maxuse_other, max(of hour1-hour24));
end;
*if it is the last record for the account or the last day of the month;
*output the observation;
if last.account or month ne month(date+1) then output;
run;