Need to learn trending, forcasting, and how to set datasets up to be able to do so

[elwayfan446]

Hi All,

I have been a basic SAS user for a couple of years now.  I have learned a lot from everyone in these communities.  I am a certified Base Programmer, however, I use more PROC SQL than anything so I am very rusty on the SAS syntax.  Most datasets I have created have been monthly snapshots of a loan portfolio over the past couple of years.  They are a snapshot in time, not historical.  I have these datasets from each month going backward.

I have been asked to start adding analysis to our reporting that tracks trends on things like delinquency as of each quarter for the past year and then project what we can expect over the next year.  We use Tableau for these types of reports and I connect my SAS datasets to them and do the summarization within them.  That being said, I don't really know where to start or where exactly I need to go to learn how to accomplish this type of reporting.  A few questions that come to mind:

1. How does my data need to be set up in order to do this type of reporting?  I have several month end tables, but that seems very difficult to try to pull together for this type of reporting.  I normally pull from each monthly snapshot to show the data at that point in time.  If I insert each record into one table, that is almost 500,000 records for each month.  To append that much each month seems like it would take forever to process.
2. Where do I learn to do the forecasting and trending?  Is there a way to do that in SAS and then just pull it into Tableau or should that type of thing be done in Tableau?  Tableau seems more of a reporting mechanism and not where the trending itself can be done.  It looks as if there are a lot of tools within SAS and SAS EG to do this type of reporting.

I primarily use SAS EG in my company so that is my primary tool.  I have to learn how to do this somewhat quickly so I am hoping you guys can point me in the right direction to learn.  I don't have a statistics background either, so that is another hurdle I must get over.  I am just a lowly SQL programmer who has learned to use SAS for the past 2 years 🙂

As always, thanks for your help.

[HB]

I would let SAS do the heavy data lifting and use Tableau for presentation. 

Look at this:

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Calgary-User-Group/Derby-Time...

I have never used PROC ARIMA or PROC FORECAST but something like that would seem to be where you would want to go. 

500K records a month?  6 million a year?  SAS can probably do that actually. 

Edit:  Also this:

http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#tfstart_toc.htm

[elwayfan446]

@HB, thanks for your reply.  This looks like great information.  I will take a look at that.  My biggest concern with that many records is just how long it will take each month to run the reports with a dataset that large.

[HB]

I run stuff on 1 million records all the time.  The job I ran a few minutes ago looked like:

200 data orphans;
201 set mydata;
202 run;

NOTE: There were 519500 observations read from the data set MYDATA.
NOTE: The data set WORK.ORPHANS has 519500 observations and 217 variables.
NOTE: DATA statement used (Total process time):
real time 10.96 seconds
cpu time 5.47 seconds

So 6 million records? Under a minute?  I dunno. 

[elwayfan446]

Interesting.  Do you submit jobs directly on the server?  I actually run all of my queries from SAS Enterprise Guide on my desktop.  I think that may cause a delay.

[HB]

I don't use Enterprise Manager. I run 9.2.  type a program, hit submit. It used to run faster but now we run it in a virtual desktop so it is just a bit slower. 

[elwayfan446]

Gotcha.  Our company did away with SAS 9.2 and made us start using SAS EG full time.  Just a different way to do the same thing.  I will research some more and play with the large dataset to see how it performs.

[SASKiwi]

Do you have access to a remote SAS server or is local SAS on your PC your only option? Once you start getting into millions of records then a remote SAS server is definitely the way to go. BTW the SAS product for forecasting is SAS/ETS.

[HB]

Our experience has sure been the other direction. Our programs manipulating multi million record files ran faster locally than they do with a remote desktop.