topic Re: multiple part question in SAS Programming

multiple part question

Aar684 — Tue, 24 Mar 2009 14:54:47 GMT

Hi all,

First time posting here. Relatively new SAS user with only basic functions used in the past.

First, I have a data in csv format. This data set contains about 30 columns. The first 5 columns are either account numbers, customer name, or some other form of customer identification. The 6th column is the date in the mm/dd/yyyy format. The last 24 columns pertain to each hour in the day ie var1 through var24. Each hour of the day has a numerical value pertaining to electricity demand. Each customer has 365 days of data with 24 hours per day.

My first problem is with importing. The csv has no column headers, which is making it difficult to import.

My second problem is more complicated. I need to find the max value in each month for each customer and output it. So say I have 500 customers each having 365 days of daily data with 24 hours of data per day. I need to find the max hour in each MONTH and output that. Help please, I am lost.

Re: multiple part question

Aar684 — Tue, 24 Mar 2009 14:58:34 GMT

045879458, 897541548, alppa, ch#1, k-0-654-81-1-1, st johns school, 045879458, 01/01/08, hour 1 value, hour 2 value..........hour24 value

That is one row of the data set I am trying to import and find the maxes for. Each customer, in this case it is st johns school has 365 days of data with the accompanying 24 hours per day. I hope this helps clarify.

Re: multiple part question

deleted_user — Tue, 24 Mar 2009 15:28:12 GMT

Well firstly you will need to read in the data using an infile and input statement. For example:

data usage;
infile 'you_file.csv' dsd missover;
input account_no $ account_no2 $ name $ ........... hour1-hour24;

I've not checked but I'm pretty sure you can use the - in your input statement. I'm afraid I can't explain here the ins and outs of infile and input but you can find it in the documentation. The DSD option treats 2 commas as a missing value (otherwise it would treat them as one comma by default) and sets the delimiter to commas by default.

secondly you want to find the maximum of your 24 hour values for each observation use the max funtion to find that. Best way is to assign it to a new variable e.g. max_usage=max(of hour1-hour24)
Notice the use of the - again to tell sas it is a range of variables.

finally you need to fins the maximum for the customer for the year. My way would be to use proc sql.

proc sql;
create table max_usage as
select *, max(max_usage) as yearly_max
from usage
group by account_no;
quit;

If you want to figure out what hour/date the maximum occurred you could put the hours into an array and run through them to see which one matches the maximum you have found. e.g

data what_hour;
set max_usage;
array hours(24) hour1-hour24;
do i=1 to 24;
if hour(i)=yearly_max then do;
max_hour=i;
max_date=date;
end;
end;
run;

There's a lot there, hope it makes a bit of sense.

EDIT: Just read your original post again. I see you want the hour of each month, just make the group by in the sql group by account_no, month assumeing you have created a month variable by using the month function ie month=month(date).

There will be many other ways to do this, quicker, smarter ways, but this is a simple way and hopefully you can get out of it what you need. Message was edited by: pznew

Re: multiple part question

Aar684 — Wed, 25 Mar 2009 18:15:32 GMT

I actually don't need the hour of the month, I need the max value returned for each month in the year. i.e. 24 hours in a day, approx 30 days in a month, what is the max value in those hours.

Re: multiple part question

deleted_user — Sat, 28 Mar 2009 18:12:24 GMT

seriously,
I think you need to hire a sas programmer

Re: multiple part question

sbb — Sat, 28 Mar 2009 20:52:55 GMT

Also, if you have a particular SAS programming problem, requesting input / feedback from the forum subscribers is a great location to get assistance.

Preferably, one post per problem/question for applicability to the SUBJECT, as stated -- not "multiple part question".

So, if you have attempted to use the code provided to you and you still have a very specific problem with said code, then it's time to open a new post (with a pertinent SUBJECT) and provide SAS code and preferably available SAS log output to share with the forum.

Info was shared in a previous reply stating what you need to consider in order to generate a max value for a particular month (in the PROC SQL code). Maybe you missed the information in the long-winded thread!

Scott Barry
SBBWorks, Inc.