Help using Base SAS procedures

Dashboard by month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Dashboard by month


Hi guys,

I have a dataset recording events for specific objetcs.

Table is like the following:

Event ID, Date.

A               12/03/2012

B               25/04/2012

A               10/05/2012

A               10/03/2012

I would like to perform a dashboard counting all the events happening for each month.

Resulting table would looks like:

               Jan      Feb      March     April     May

Event A   0        0          2             0             0

Event B   0        0          0             1             0

My issue is that  the table is huge (>1billion records) therefore i need to use the index on the date field.

So how i can perform that task in an efficient way? I understand i could create an additional field for the month and summarize on that field but is there any way i could summarize directly on the date and then output the month? (such as with some categorization method?)

Thanks for all your help in advance!


Accepted Solutions
Solution
‎01-17-2013 03:40 PM
Super User
Posts: 17,868

Re: Dashboard by month

Is it monthly by year or just monthly? Ie Jan includes 2012 and 2011...

You could look at the monyy7. or monname9. format.

A standard proc freq with a format applied to the date would work.

proc freq data=have noprint;

table date*event/out=summary1;

format date monname9.;

run;

View solution in original post


All Replies
Solution
‎01-17-2013 03:40 PM
Super User
Posts: 17,868

Re: Dashboard by month

Is it monthly by year or just monthly? Ie Jan includes 2012 and 2011...

You could look at the monyy7. or monname9. format.

A standard proc freq with a format applied to the date would work.

proc freq data=have noprint;

table date*event/out=summary1;

format date monname9.;

run;

Occasional Contributor
Posts: 13

Re: Dashboard by month


Very simple indeed! When using the format command is SAS going to use the index to perform the summary?

Thanks a lot!

Super User
Super User
Posts: 6,502

Re: Dashboard by month

Not sure what you mean by using the index?  If you want the report for only a particular period then use a WHERE statement. That will use the index.

If you want it to get the counts from the index instead of from reading the actual data then I do not believe SAS will do that.

Of course with data that large perhaps you actually already have it in a database (such as Oracle or Teradata) in which case SAS might automatically get the database to count for you and only pull out the summary data from the database.

Occasional Contributor
Posts: 13

Re: Dashboard by month


Hi Tom,

About the index my thoughts basically is that if had created a field called "Month" in the table i could then index that field and then summarize on it. Maybe that would be an efficient way to create dashboards? Or is it better as you mentioned before to just let the DB create intermediary tables overnight etc without adding addtional fields?

I am also wondering if by using "format" the database would be able to detect the start and end of each month  (the first and last index number in the table) not requiring me to create that additional field. That would be some pretty neat functionality but i doubt it work that way.

Apologizes if my questions do not make too much sense i have started only recently to work on very large dataset, got minimal knowledge on how indexes are working and want to make sure i do not kill the CPU time of the server ,-)

Super User
Posts: 17,868

Re: Dashboard by month

Set your options so SAS can tell you if its using an index or not, if your interested. I think Tom's correct, the Index will only get used in a where statement.

option msglevel=i;

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Super User
Posts: 5,085

Re: Dashboard by month

Pontch,

PROC FREQ works just fine on unsorted data, so there's no reason to use an index for this sort of a table.  There are very few columns in the final result, and PROC FREQ can easily track all the months at the same time (unless you have an extraordinary number of different events).  Using an index would force SAS to do a lot of extra work:  find the next block of records for the same date, move that block into memory, then process records in that block that match the right date.  Then repeat that process for the next set of records that match the same date.  It's unnecessary and time-consuming to move blocks of data into memory.  Just let SAS process the records sequentially. 

Good luck.

Occasional Contributor
Posts: 13

Re: Dashboard by month

Ok thanks a lot, all your answers truely help clarify things for me!

Would anyone be aware of good document explaining how to optimize queries in SAS?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 244 views
  • 6 likes
  • 4 in conversation