My start-up predictive analytics organization “Predictive Performers” wants to do some internal planning. We receive extract files from an an accounting service each month that provide the total hours billed per each of our consultants, along with each consultants hourly rate. The files are saved to a folder on our network:
The files also breakout my “on staff” consultants as “fte=1”, so it would be interesting to see what we can compare between our full time analytic experts and our contractors.
MATCH and AGGREGATE
Using SPSS Syntax, a simple script can be used to automate the merging of these monthly files into a single “quarterly file” and then run a simple aggregation on the file producing some interesting results for the quarter. The script below uses the GET FILE to identify and sort each montly file, then MATCH FILES to perform the merging and finally AGGREGATE to produce some statistics.
In this Syntax script, I’ve output the results to a merged quarter “aggregation” file that gives us a mean and median hourly rate by month for FTE’s and contractors. It also gives us a median for “total hours billed”:
Here are the 2 new files – the “a” version contains the aggregated results.
Working your way through the SPSS Statistic menus gets old fast. This (very) simple example is an example of the efficiency and control that Syntax provides.
Remember “Scripts over Menus”!