BookmarkSubscribeRSS Feed
Aar684
Calcite | Level 5
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.
5 REPLIES 5
Aar684
Calcite | Level 5
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.
deleted_user
Not applicable
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
Aar684
Calcite | Level 5
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.
deleted_user
Not applicable
seriously,
I think you need to hire a sas programmer
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.

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
  • 5 replies
  • 593 views
  • 0 likes
  • 3 in conversation