Dashboard by month

[Pontch]

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!

[Reeza · Accepted solution]

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;

[Pontch]

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

Thanks a lot!

[Tom]

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.

[Pontch]

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 ,-)

[Reeza]

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

[Astounding]

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.

[Pontch]

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?