Teaching Macros to Play Nice with the other Data Sets

Written by: Jennifer Thompson

Macros and automation can save so much time, ulcers, gray hair, etc. In STATISTICA, creating macros to automate tasks is as easy as hitting record on your DVR. They are fast, and they make sure the analysis is done consistently with the same options and analysis procedures. One drawback, when comparing macros to people, is macros don’t think for themselves. They just run the script, even if the analysis it is performing is absurd. So they may need taught to play nice with their data sets.

Recorded macros reference the variable’s position in the spreadsheet. The macro can be run on this data set as often as needed, always giving the proper results. If a new variable is added or one is deleted, the position of the variables for analysis may change.  A new data set may also organize the variables differently. If this is the case, running the macro will no longer give the expected results. Where the macro was supposed to build a regression model between X and Y, it is now treating X as dependent and using ID number as the independent variable. Chaos ensues.
What if there was an easy way to customize the macro to protect against this potential problem? I’ll give you two. The easy way is to simply remove the variable reference and the then you will select variables each time the macro is run. The more involved method takes a few steps to have the macro reference the variables by name, but the resulting macro runs without your input.

Method 1: Delete Variable Reference

Delete the variable selection portion of the macro. Variable selection in recorded macros behaves basically the same way regardless of analysis or graphing procedures used. The macro selects variables similarly to how it’s done in this example. The variable selection line is oAD2.Variables = “3-5”. Variables 3 through 5 will be used for this descriptive statistics analysis.

Macro 1

By deleting 3-5, leaving empty quotes, the variable position reference is removed. Now running the macro will prompt you to select appropriate variables. As is typical of life, taking the easy option now, means more work in the future. Each time you run this macro, you will need to select variables for analysis. The plus side is that the macro is compatible with any data set now.

Macro - Select Variables

Method 2: Customizing Macro to Reference by Name

The alternative is to add some custom, yet simple, programming to reference the variables by the name. Then variables can be added, deleted, rearranged, etc., and the macro will still use the proper variables. The macro can be used on a new spreadsheet with the same variable names with no issues.
I will not claim that my way is the only way that works, or that it is the best way. (It may be the best way.) If nothing else, this example can spark ideas for improving your custom macros in STATISTICA!
I started with a loop to access the variable names and pick out the three variable names of interest. You may recognize them from an example data set, Adstudy.sta. They are MEASURE01, MEASURE02, and MEASURE03. Then I modified the variable selection line to use references based on variable names. The steps are listed below.
  1. Reference the spreadsheet, S1.
  2. Create placeholders, v1, v2 and v3, for the 3 variable positions.
  3. Create an array, VarList, for storing the variable names.
  4. In a loop, find the spreadsheet position of each of the variables and store them in the placeholders created in step 2.

Macro - Add Loop

  1. Modify the recorded macro, variable selection line, using the variable position variables.
  2. Delete any unnecessary lines of recorded code for simplicity.*

Macro - Modify Recorded section

Now running the macro will select the variables named MEASURE01, MEASURE02, and MEASURE03, regardless of their position in the spreadsheet. The macro is ready to play nice with other data sets.
 *Note: Recorded macros list all available options and settings. This makes modifying the macro much easier later on. It is easier to change an option from “True” to “False” than to come up with the line of code to access that option. For the purpose of showing others how a macro works, it helps to clean up the code, removing unnecessary parts. So I removed the lines of the recorded macro that simply set the default settings. The macro works the same with or without these lines.

About statsoftsa

StatSoft, Inc. was founded in 1984 and is now one of the largest global providers of analytic software worldwide. StatSoft is also the largest manufacturer of enterprise-wide quality control and improvement software systems in the world, and the only company capable of supporting its QC products worldwide, with wholly owned subsidiaries in all major markets (StatSoft has 23 full-service offices, on all continents), and its software is available in more than 10 languages.

Posted on October 25, 2012, in Uncategorized. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: