STATISTICA Blogs

So I took my dog, Pharyn, for a run the other day. Nothing exceptionable about that, save for the fact that it was JANUARY 28TH and very warm!!! Normally it is very cold this time of the year. 

But anyway, we had a good time. Actually, I should probably say that she had a good time – having not run for a couple of weeks made her seem like a Ferrari driving through a school zone. She felt restrained and just dying for a chance to open the throttle wide and take off in a sprint. Unfortunately, I’m not in her league when it comes to endurance, so my huffing along held her back. Afterward, as I was lying on the floor trying to recover and she was licking my face so hard that my nose started to bleed, we had the following conversation:

Pharyn- I just wanted you to know, Bob, that I wrote a little macro to answer a question from a current customer. The customer asked, “Is there an easy way to change the order of variables within a spreadsheet so numeric variables are first?” You may want to post it on the blog.

Pharyn is right. It is a very ‘little macro’ that I’ll post below. She’s also irritating, because this means that she’s been reading my work e-mail again (Bad Dog!!!). Before posting the macro, I’ll note that this task can also be done manually, of course, but this can become cumbersome for anything other than the smallest of datasets/spreadsheets. 

Here’s a screenshot of the macro, and I’ll discuss the elements below the shot (Yes, Pharyn omitted error handling. Again. Some tricks you just can’t teach a dog.  Fortunately, you know better than this…right?).

The first few lines of the macro are self explanatory: oSS is the dataset with the variables that you wish to sort. The long integer variable ‘l’ is a counter, and the long integer variable ‘p’ is a placeholder. The logic is very straightforward: The macro steps through every variable in the spreadsheet, inspecting it to determine if it is of type double precision decimal (0) or integer (2). Note: If you’re not certain of the numerical representations of the variable types, you can use the property Spreadsheet.VariableType([Variable Number]) to display the values using either the debug window or a message box.

The macro then moves any variables found that are numerical to the beginning of the dataset, immediately after the most recently-found numerical variable. The statement beginning “If = 1 then….” performs no variable movement because if l = 1 then we’re dealing with the first variable, which obviously doesn’t need to be moved. The step simply reassigns p to 1 so that any additional numerical variables found will be placed after the first variable; otherwise, the first variable found would be placed after variable p (=0), which is the column holding the case names, so the variable would be moved to the very beginning of the dataset. The macro logic further ensures that the numerical variables, while segregated at the beginning of the dataset, nevertheless retain their same relative order within the dataset. This retention of relative order is also true for any non-numerical variables as well. 

Here’s a sample dataset we created just for this macro:

Starting with the first variable, every other variable is of type Text. The even-numbered variables are of type Double, with the exception of Var6, which is of type Integer.  Now let’s see what this dataset looks like after being processed by this macro:

Note how all of the numerical variables, both double and integer, are at the beginning of the dataset, while all of the text type variables follow after them. Also please note how the variables have retained their relative order: that is to say, the first numerical is still the first numerical variable; the last numerical variable, still the last. The same is also true for the non-numeric variables.

You can of course modify this macro further to place all of the doubles first, and then the integers, then the text types, etc. Doing so is very straightforward and wouldn’t require much additional code. So that’s it; I hope that you’ve found this macro helpful. Now if you’ll excuse me, I have to go discipline Pharyn with a rolled-up newspaper for omitting error-handling. She’ll never learn if she’s allowed to get away with this kind of behavior.

Image Credit (First Image): Books Sorted By Color http://www.flickr.com/photos/torstenkrohn/2666766691/