An off-topic spot to chat about your musings of the day

A real-life example of a data analytics "stumper"

Reply
Regular Contributor
Posts: 233

A real-life example of a data analytics "stumper"

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:

Screen Shot 2016-12-18 at 8.49.09 PM.png

 

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:

  1. Red - No group had used the product in the time period
  2. Yellow - The last (or only) group to use the product was more than 1 year ago, and the total volume for that customer was <6
  3. Green - 4 or more groups have used more than 12 items per group in the last year

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.  

 

Screen Shot 2016-12-18 at 8.48.53 PM.png

 

So I tried a number of different SQL statements, and the closest I could get looked like this:

Screen Shot 2016-12-18 at 9.23.38 PM.png

 

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!

 

Happy Coding!

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Contributor
Posts: 43

Re: A real-life example of a data analytics "stumper"

Have you tried Proc Tabulate?  Something like this:

 

(untested code)

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.;
run;

Regular Contributor
Posts: 233

Re: A real-life example of a data analytics "stumper"

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!

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Contributor
Posts: 43

Re: A real-life example of a data analytics "stumper"

If this works or gets you close -try adding the ODS elements to color code your output

 

Tammy

Regular Contributor
Posts: 233

Re: A real-life example of a data analytics "stumper"

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!

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Community Manager
Posts: 2,842

Re: A real-life example of a data analytics "stumper"

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.

Regular Contributor
Posts: 233

Re: A real-life example of a data analytics "stumper"

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

Chris

Has my article or post helped? Please mark as Solution or Like the article!
Ask a Question
Discussion stats
  • 6 replies
  • 533 views
  • 7 likes
  • 3 in conversation