Minitab - Getting a Minitab Macro to look up a Column Name

T

Trippy

Please excuse me if there's a really obvious answer to this question. I work in local government, and somehow the job of finding a statistics package has fallen on my desk.

That being said, alhough I like how easy it is to use, i've been using the trial version of Minitab for about 3 days.

I'm in the process of writing a macro to compare box and whisker plots for water quality data for the last N samples against the total population of all samples collected to date, and referencing these box and whisker plots, which include custom quantiles, and 2SE Confidence intervals.

What i'm aiming for is a macro that my co-workers who have no little experience of statistics can use, so they can simply type in:
Code:
%COMPARE 'ANALYTE' N
And Minitab will do the rest of the work for them (as long as the first row of their spreadsheet is column names).

Here's what i've managed to figure out so far.
Code:
Macro
COMPARE ANALYTE1 J DATA1 DATA2 DATA3 DATA4
MCOLUMN ANALYTE1 ANALYTE2 DATA1 DATA2 DATA3 DATA4
Mconstant K1 K2 K3 K4 K5 K6 K7 K8 K9 K10 K11 K12 TEMP1 I J H
LET H=COUNT(ANALYTE1)
LET I=H-(J-1)
do I=I:H
  LET ANALYTE2[I]=ANALYTE1[I]
ENDDO
LET K1=PERCENTILE(ANALYTE1,DATA1[1])
LET K2=PERCENTILE(ANALYTE2,DATA1[1])
LET K3=MEAN(ANALYTE1)-STDEV(ANALYTE1)/SQRT(N(ANALYTE1))
LET K4=MEAN(ANALYTE1)+STDEV(ANALYTE1)/SQRT(N(ANALYTE1))
LET K5=MEAN(ANALYTE2)-STDEV(ANALYTE2)/SQRT(N(ANALYTE2))
LET K6=MEAN(ANALYTE2)+STDEV(ANALYTE2)/SQRT(N(ANALYTE2))
LET K7=CONCATENATE("Consent Limit - ",DATA2[1]," (",DATA3[1]," ",DATA4[1],")")
LET K8=CONCATENATE("Consent Limit - ",DATA2[2]," (",DATA3[2]," ",DATA4[2],")")
LET K9=DATA3[1]
LET K10=DATA3[2]
LET K11=EXP(AVERAGE(LOGTEN(ANALYTE1)))
LET K12=EXP(AVERAGE(LOGTEN(ANALYTE2)))
Boxplot ANALYTE1 ANALYTE2;
  Overlay;
  Proportional;
  SOffset;
  IQRBox;
  Outlier;
  Mean;
    Type 18;
    Size 1.5;
  Reference 2 K9;
    MODEL 1;
    Label K7;
  Reference 2 K10;
    MODEL 1;
    Label K8;
  Title;
  Footnote;
    FPanel;
  Marker 2 K6;
    Unit 1;
    Type 28;
    Color 2;
    Size 2;
  Marker 2 K5;
    Unit 1;
    Type 19;
    Color 2;
    Size 2;
  Marker 1 K4;
    Unit 1;
    Type 28;
    Color 2;
    Size 2;
  Marker 1 K3;
    Unit 1;
    Type 19;
    Color 2;
    Size 2;
  Marker 2 K2;
    Unit 1;
    Type 11;
    Color 2;
    Size 2;
  Marker 1 K1;
    Unit 1;
    Type 11;
    Color 2;
    Size 2;
  Marker 1 K11;
    Unit 1;
    Type 8;
    Color 16;
    Size 2;
  Marker 2 K12;
  NoDTitle.
endmacro
I've figured this much out by manually adding (for example) the points, and seeing what changes it made to the command code (Not to mention much perusing of help files).

What I need the help with is the most straight forward thing (I think) I can do is to say to people "Include the information relating to the permit limits under columns with these names", so that I can replace DATA1, DATA 2, DATA3, and DATA4 with those colum names, but so far everything i've tried has failed with a variety of error messages.

My question is this - How do I get a Macro to look up a column name, rather than a column number, without specifiying it in the commandline when I initiate the macro?

In other words, if I was executing it as an exec, or on the command line I would just type in:
Code:
LET K1=PERCENTILE(ANALYTE1,'PERCENTILE'[1])
But if I include that line in the macro it gives me the error message:
Code:
LET K1=PERCENTILE(ANALYTE1,'PERCENTILE'[1])
                           X
* ERROR * Cannot use quoted string to refer to macro variables
* ERROR * Completion of computation impossible.
 
Last edited by a moderator:

Miner

Forum Moderator
Leader
Admin
Re: Minitab Question - Getting a Macro to look up a column name

Bumped. Any takers?
 
A

Allattar

Re: Minitab Question - Getting a Macro to look up a column name

Without looking at anything, a simple answer is not to use column names.

Typing the command
%compare c1-c6

will send a command to Minitab to read in columns c1 through to c6. Referring to column c numbers rather than names.

You have to change the notation at the beginning of the macro to

Macro
COMPARE DATA.1-Data.n
MCOLUMN DATA.1-Data.n

That is the first one I can think of for the moment as a simpler solution.
Becuase a local macro reads the data into an internal worksheet you cannot then pull new columns in from outside the macro unless they where sent to the Macro at the start.

What can work instead is using an Exec (basically a mini macro with no headers, just the command language) to read in the columns in the worksheet and then send them to the Macro.

Of course then I have to remember the command for counting columns in the worksheet.
 
T

Trippy

Re: Minitab Question - Getting a Macro to look up a column name

Without looking at anything, a simple answer is not to use column names.

Typing the command
%compare c1-c6

will send a command to Minitab to read in columns c1 through to c6. Referring to column c numbers rather than names.

You have to change the notation at the beginning of the macro to

Macro
COMPARE DATA.1-Data.n
MCOLUMN DATA.1-Data.n

That is the first one I can think of for the moment as a simpler solution.
Becuase a local macro reads the data into an internal worksheet you cannot then pull new columns in from outside the macro unless they where sent to the Macro at the start.

What can work instead is using an Exec (basically a mini macro with no headers, just the command language) to read in the columns in the worksheet and then send them to the Macro.

Of course then I have to remember the command for counting columns in the worksheet.

Thanks for the feedback, I greatly appreciate the help.

I emailed minitab support, and they said to use KKSET, but that seems to be creating a different error within the macro (something along the lines of inappropriate use of text).
With using this:
Code:
Macro
COMPARE DATA.1-Data.n
MCOLUMN DATA.1-Data.n
Does the column range have to start at one, or is that just a notational convention?

For example, if I was to use:
Code:
Macro
COMPARE DATA.1-Data.n
MCOLUMN DATA.1-Data.n

But then type in COMPARE C33-C36 would that still work?
If it does, that would be (potentially) an ideal solution, as long as I can use a DO/ENDDO loop to read consecutive columns at the same row.
 
T

Trippy

Re: Minitab Question - Getting a Macro to look up a column name

Actually, on second thoughts, ignor emy last post, a little bit of trial and error answered my questions. :)
 
A

Allattar

Re: Minitab Question - Getting a Macro to look up a column name

Ok glad it works.

A quick way to see how the notation works is to use the following.

Macro
Hist x.1-x.n
mcolumn x.1-x.n
mconstant i
do i = 1:n
histogram x.i
enddo
endmacro

Yes there are simpler ways to create a lot of histograms but it is just to highlight how the array of columns works. As you should have seen by now you can use any range of columns.
 
T

Trippy

I wound up converting it to a global macro (once I realized the coding for a global macro was essentially the same as an exec file), and wound up using CK notation, and prompting for user input (friendlier for my coworkers, they click a button, answer some questions, and get their graph).

I could post the code, once i've cleaned it up a little (still one very small bug needs squished, and some profane 'NOTE's need removed (what can I say, I got bored with 'NOTE HELLO 1' as a fault finding tool).

One question though, is it possible to use conditional statements on a subcommand for a graph, or do you need to do that external to the graphing command.
 
A

Allattar

I find im very boring when error checking with note.

Note 1
Note 2
Note 3
etc...
But its very powerful to help you find where the macro stops.

Of course if you where feeling very adventurious you could leave the Local macro in place as it is, but use a global macro to get the user input and fire that to the local.
 
T

Trippy

I find im very boring when error checking with note.

Note 1
Note 2
Note 3
etc...
But its very powerful to help you find where the macro stops.
Which is precisely what I was doing and why I was doing it :). Only as I say, I got tired and got imaginative, and colourful.

Of course if you where feeling very adventurious you could leave the Local macro in place as it is, but use a global macro to get the user input and fire that to the local.
Naww, i'll pass for now thanks.

The Macro's a little bulky, and has almost no error checking built into it (save for two things I expect to happen), but it does exactly what I want, and if I can convince the IT department to instal Minitab is going to shave literaly hours off the reports that I write, and save others time as well.

Although, if you have some suggestions on streamlining the code, i'd be interested in hearing them, and i'll post the code for you.

Currently i'm working on my next Macro, which is essentially a sorting Macro, which divides a data set up by sample site, then by analyte (Ideally seperate sample sites would be on seperate worksheets), and then checks to make sure that the dates match up not only across analytes at the same type (some times the permit holder will neglect to test for something) but also makes sure that the dates match up between sites (sometimes the discharge sample might be taken, but the upstream or downstream might not be).

I need to do this to make sure that timeline graphs match up properly (if they don't it could conceal trends or correlations) and sometimes the missing data has as much to say as the data that's present (one might, for example, decide to querey why the last sample of the month is always missing).
 
A

Allattar

The easiest way to streamline things in a macro is to include several submacro's in the file. Use Call to access them, that way if you are repeating a bit of code a few times, you can call the submacro and save a few lines.

This can be where mixing Globals and Locals can work well. Be aware you can call a Local macro from a Global, but you cannot call a global from a local.

Its odd but its becuase the Global works off data in the worksheet. The Local takes columns and constants internal to itself. So a global can send columns into a local, and it can spit them out. But if you asked a local to call a global, the global doesn't know what is being used by the local.
 
Top Bottom