turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- help with max function

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2010 11:28 AM

Hi all,

I have a csv dataset that includes an account number, a date, and 24 hours of data per day. Each row contains one day of a month of the year. All account numbers have 365 days.

So the sample data set is as follows:

account# date hour1 hour2 hour3 ...............hour24

There are around 50 account numbers in the data set totaling somewhere in the range of 4000 or so rows. I need to find the maximum value by MONTH and return that value for each account number for each month. So, in the end I will have 12 max values for each account number. HELP! PLEASE. The code, minus the import that I have so far is as follows :

data usagesum;

set kva end = eof;

retain maxuse;

month = month(date);

if (day(date) = 1) and not (_n_ = 1) then do;

maxuse=0;

end;

maxuse= max(of hour1 - hour24);

if last.account or month ne month(date+1) then output;

run;

I am getting the max but not of the entire month. Message was edited by: Aar684

I have a csv dataset that includes an account number, a date, and 24 hours of data per day. Each row contains one day of a month of the year. All account numbers have 365 days.

So the sample data set is as follows:

account# date hour1 hour2 hour3 ...............hour24

There are around 50 account numbers in the data set totaling somewhere in the range of 4000 or so rows. I need to find the maximum value by MONTH and return that value for each account number for each month. So, in the end I will have 12 max values for each account number. HELP! PLEASE. The code, minus the import that I have so far is as follows :

data usagesum;

set kva end = eof;

retain maxuse;

month = month(date);

if (day(date) = 1) and not (_n_ = 1) then do;

maxuse=0;

end;

maxuse= max(of hour1 - hour24);

if last.account or month ne month(date+1) then output;

run;

I am getting the max but not of the entire month. Message was edited by: Aar684

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

02-19-2010 11:35 AM

You are using what's called "BY GROUP PROCESSING" -- the FIRST. and/or LAST. must also include a BY statement. So, you will need to derive all variables, sort the data, then have a DATA step with your max-value derivation logic.

Scott Barry

SBBWorks, Inc.

Recommended Google advanced search argument, this topic/post:

by group processing site:sas.com

Scott Barry

SBBWorks, Inc.

Recommended Google advanced search argument, this topic/post:

by group processing site:sas.com

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-19-2010 11:44 AM

Very confusing. Is my max-value derivation logic even remotely correct?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

02-19-2010 11:56 AM

Scott,

I am sorry. My knowledge is very basic. I was getting help from someone here who isn't available to me at the moment. I am lost, but would like to learn. Unfortunately I have a tight time constraint. Am I any where near where I need to be?

I am sorry. My knowledge is very basic. I was getting help from someone here who isn't available to me at the moment. I am lost, but would like to learn. Unfortunately I have a tight time constraint. Am I any where near where I need to be?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

02-19-2010 12:01 PM

You are missing the BY ACCOUNT MONTH;

statement after the SET statement.

statement after the SET statement.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Flip

02-19-2010 12:22 PM

Yes, you are close, but your MONTH derivation code must be in a separate DATA step. You cannot have a SET with a BY where the variable is not yet derived -- that's why you need multiple DATA steps -- suggest adding a SORT as mentioned.

Scott Barry

SBBWorks, Inc.

Scott Barry

SBBWorks, Inc.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

02-19-2010 11:35 AM

Try changing the third from the last line from

maxuse= max(of hour1 - hour24);

to

maxuse= max(of maxuse hour1 - hour24);

. That should include the max from the previous records in the month.

Doc Muhlbaier

Duke [This is in addition to Scott's comments.]

Message was edited by: Doc@Duke

maxuse= max(of hour1 - hour24);

to

maxuse= max(of maxuse hour1 - hour24);

. That should include the max from the previous records in the month.

Doc Muhlbaier

Duke [This is in addition to Scott's comments.]

Message was edited by: Doc@Duke

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Aar684

02-19-2010 01:17 PM

OK. You were close. I think you can actually do this without BY-group processing; my code is not tested. :

PROC SORT DATA=kva; BY account date; RUN;

* assumes dates are SAS dates, not text strings;

data usagesum;

set kva; * end = eof; * end not needed.;

retain maxuse;

month = month(date);

if (day(date) = 1) /* and not (_n_ = 1) */ then do; * RETAINed variables initialized to 0 for _N_=1 already.;

maxuse=0;

end;

maxuse= max(of MAXUSE hour1 - hour24); * added maxuse to accumulate through the month.;

PUT account date maxuse; * debugging code to see if the accumulatior is working;

if /* last.account or */ month ne month(date+1) then output; * "last" not needed.;

run;

Give this a try and see where you get.

Doc

Message was edited by: Doc@Duke Message was edited by: Doc@Duke

PROC SORT DATA=kva; BY account date; RUN;

* assumes dates are SAS dates, not text strings;

data usagesum;

set kva; * end = eof; * end not needed.;

retain maxuse;

month = month(date);

if (day(date) = 1) /* and not (_n_ = 1) */ then do; * RETAINed variables initialized to 0 for _N_=1 already.;

maxuse=0;

end;

maxuse= max(of MAXUSE hour1 - hour24); * added maxuse to accumulate through the month.;

PUT account date maxuse; * debugging code to see if the accumulatior is working;

if /* last.account or */ month ne month(date+1) then output; * "last" not needed.;

run;

Give this a try and see where you get.

Doc

Message was edited by: Doc@Duke Message was edited by: Doc@Duke

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Doc_Duke

02-23-2010 08:33 AM

Ok guys. Trying your suggestions and will report back. Didn't have a chance to look at this again as I was out sick. I am going to plugging away today. Thanks all.