12-18-2016 09:38 PM
I had an opportunity to meet with a group of MBA students who are taking classes using SAS, and one of the things we talked about was the lack of real life examples in their classes. They have been working through a lot of excercises, but they are all "neat and tidy" - cleaned data, code based on examples already available, with answers they knew were "right" or "wrong". I mentioned that there are a lot of great posts on SAS-L and here on the Communities site where people post questions, and that they should take a look and see if they can resolve the problem.
I'm going to try and start posting examples of issues that I face in my daily work; the data will be de-identified, but the code etc. will be what I've used. This first one is something I still have not figured out, and ended up having to finish the report in Excel (with a lot of manual work).
Here's the screen shot of the output that I was requested to provide:
The dataset was our Inventory Dictionary, and I needed to provide the Last Date each item was used, and the total volume, from April 1, 2015 to November 1, 2016. I needed to group the data by Customer, so we could see the high volume users. As an additional complexity, I needed to highlight the product codes according to the following criteria:
The thinking is that the products in Red we can remove from our inventory, those in Yellow can be reviewed and possibly removed, and those in Green need to be reviewed to see if we need to modify our purchasing model or look at other options for getting the item.
Here's a screen shot of the dataset. The real dataset had over 100,000 rows, with 4,000 distinct product codes and 9 groups.
So I tried a number of different SQL statements, and the closest I could get looked like this:
It's close, but I couldn't figure out how to the rows merged into a single row of data. This was the dataset I ended up moving to Excel, and then spent almost 8 hours manually moving the data around, and then used Excel Functions to help me filter out the data to colour code.
So my question / challenge to you - how would you do this report in SAS? If you were handed this request and asked to have it done in 24 hours, what steps would you take? I would appreciate any thoughts / suggestions you have!
12-19-2016 07:21 PM
Have you tried Proc Tabulate? Something like this:
proc tabulate data=testit missing;
class product group;
var last_date volume;
table product='Product Code',group=' ' * (last_date='Last Used'*mean=' ' volume='Volume'*sum=' '*f=comma8.);
format last_date mmddyy10.;
12-19-2016 07:46 PM
Hi @TMiles ! Thanks for the reply - I did fiddle with PROC TABULATE but couldn't figure it out (first time I've used it) and I didn't have time to really play with it. I'll go back and see if your code (or a modified version) will do what I need - thanks again!
12-19-2016 09:26 PM
Hi Tammy! I've used PROC TEMPLATE and ODS Graphics before, so I knew I could do that once I figured out the table - getting the table was my problem LOL.
thanks so much and have a good evening!
12-20-2016 08:29 AM
If you have SAS Enterprise Guide, the Summary Tables task is a nice point-and-click interface that generates very clean PROC TABULATE code. It would probably get you most of the way to your report, and then you could tweak the code with the colors you want.
For a more robust report, use a FORMAT to define the color ranges so that you can easily change the ranges if the business rules change.
And finally, since you have so much data and some of the results might be used for a recommended action (stop ordering a certain product), consider creating an "executive summary" report that lists just those items -- a nice TL;DR section for the folks in charge of inventory.
12-20-2016 08:33 AM
Thanks Chris - As always your insight is very helpful. I admit since i've started using SAS Studio, my EG usage has dropped off - I really should get back into it, and this report may motivate me (they're talking about me running this report monthly, and I do not have the mental fortitude to that much manual work every 4 weeks!).
I love the idea of an executive sumamry - definitely something that should be used more often. I think a lot of good findings get lost / not read because they're lost in "analytic-speak".
Shall keep you posted :-)