BookmarkSubscribeRSS Feed
Aar684
Calcite | Level 5
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)?
21 REPLIES 21
Flip
Fluorite | Level 6
Proc sql;
select max(var) from ...
Aar684
Calcite | Level 5
I need some serious help. Is there any way I can post an example of a data set to ask questions about?
Aar684
Calcite | Level 5
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
Fluorite | Level 6
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
Calcite | Level 5
Very confusing. Can you clarify a little bit? Keep in mind my SAS knowledge isn't nearly what yours is.
Aar684
Calcite | Level 5
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
Fluorite | Level 6
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
Calcite | Level 5
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
Not applicable
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
Calcite | Level 5
If I new the solution I wouldn't be asking............
Aar684
Calcite | Level 5
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
Not applicable
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
Not applicable
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 21 replies
  • 1473 views
  • 0 likes
  • 4 in conversation