Pages

Monday, 1 November 2010

SAS Transpose

Last week I visited a client who had been trialling Alteryx.  The team who had been conducting the trial were SAS analysts and were particularly impressed by Alteryx's spatial capabilities.  At the end of the meeting I asked if there was anything which they thought they could do in SAS and not in Alteryx.  The reply was the only thing was that the transpose tool didn't really to seem to behave as they expected given their experience of SAS's Proc Transpose.

So this weekend I thought I would take a look at what the differences might be.  Now it has been a long time since I have written anything in SAS, so first step was a quick google to find out how the Proc Transpose tool worked.  I came across this excellent tutorial:

http://support.sas.com/resources/papers/proceedings09/060-2009.pdf

I decided I would recreate the lessons from this document using Alteryx.  Upon starting I realised why they had been having trouble, the Proc Transpose function of SAS is closer to an Alteryx cross tab than transpose,  but in actual fact to reproduce the results exactly Alteryx needs a transpose and cross tab tool. 

Just to be clear it is not that Alteryx tools aren't as efficient as SAS they are just different and I have added an extra example to each lesson which I have titled "The Alteryx Way" which shows what I think an Alteryx analyst would do to transform the example data.

I could have tried to turn my modules into a SAS Proc Transpose macro for Alteryx however I don't believe that would be the most efficient way of doing things in Alteryx.  I am not presenting these examples as the best way of doing things in Alteryx; more of a way for SAS analysts to get a handle on the differences between the two tools.  I think if you are going to program efficiently in any language you need to think about your solution in terms of how that language works.

My example modules can be downloaded below:



As always feel free to post any comments/questions here or e-mail me directly.