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

T

Trippy

#1
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:
Elsmar Forum Sponsor
A

Allattar

#3
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

#4
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

#5
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

#6
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

#7
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

#8
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

#9
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

#10
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.
 
Thread starter Similar threads Forum Replies Date
J Response Surface Design - Repeats - Getting data into Minitab 15 Design and Development of Products and Processes 2
R Select the 1 Supplier based on the Parts Durability from 6 Supplier Samples using Minitab Using Minitab Software 11
A One-factor and multiple-response Minitab optimization Using Minitab Software 0
O Problem in Minitab - ERROR * Factor Voltage is highly correlated with other terms Using Minitab Software 10
F Minitab 19 Anova or X bar # of distinct catagories Using Minitab Software 2
P Pooled Standard Error - Minitab Statistical Analysis Tools, Techniques and SPC 0
S Asterisk in DOE minitab software Using Minitab Software 23
B Does anybody know how to get older versions of Minitab to work in Windows 10? Quality Tools, Improvement and Analysis 9
M Minitab Capability of the Population (no sampling) Using Minitab Software 11
Z Minitab - Updating Graph with specific data points Using Minitab Software 2
S Need help with analysing a survey on minitab Using Minitab Software 1
M Minitab assistant: 1-Sample % Defective Test Using Minitab Software 1
0 Interesting Discussion Analysis of half normal distribution in minitab Using Minitab Software 11
G Batch printing reports in Minitab Using Minitab Software 3
B Minitab Type 1 Gage Study on True Position Question Measurement Uncertainty (MU) 1
V Minitab GRR crossed Xbar and r method macro needed Using Minitab Software 9
I Number of decimals in equation calculated by MiniTab Using Minitab Software 0
A Acceptance p-value for linearity and bias analysis in minitab results Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 0
M How to change the display of optimization plot in Minitab 18 Using Minitab Software 2
M Minitab tool to evaluate PM (Preventive Maintenance) process Using Minitab Software 6
E Input to ANOVA 1-WAY - How can I perform this analysis in minitab? Using Minitab Software 2
N Tolerance Interval plots in Minitab Statistical Analysis Tools, Techniques and SPC 0
J Including Repeats in DoE using Minitab Using Minitab Software 5
A Minitab 18: How to automate GRR Summary Table save Reliability Analysis - Predictions, Testing and Standards 1
N What subcommands can be used with "Stop" in Minitab 18? Using Minitab Software 8
S Minitab - Factor Analysis: Label on second series of data for Biplot Using Minitab Software 4
J Which Anova dropdown to use for R&R in Minitab? Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 1
J DoE - Number of runs in Plackett-Burman (Minitab) Using Minitab Software 2
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
A Generating samples from a population in Minitab Using Minitab Software 0
Ron Rompen Random data generation in Minitab Statistical Analysis Tools, Techniques and SPC 2
I DOE: High variance and small effects in Minitab Using Minitab Software 1
B Informational Expanded Gage R&R Analysis using Minitab Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 18
R Rank Deficiency Error Minitab Using Minitab Software 7
H Minitab 15 - Factorial Design - 3 factors: 4x3x2 - How to? Using Minitab Software 4
A Capability Analysis for Packaging Seal Strength with spec. >0.1 Kgf using Minitab Using Minitab Software 6
Coury Ferguson MiniTab 17 Question-Standard Deviation Default Capability, Accuracy and Stability - Processes, Machines, etc. 5
N Regression Model (Minitab 18) - Which values my input parameters should have Using Minitab Software 2
L MINITAB 17 - Changing Data Using Minitab Software 3
R Conjoint Analysis - Minitab 18 Statistical Analysis Tools, Techniques and SPC 2
F How to make an ANOVA of a design with repeats (not replicates) in Minitab? Using Minitab Software 18
W Definitive screening design in Minitab 17 Manufacturing and Related Processes 1
S Minitab runs at different speeds on identical PCs Using Minitab Software 5
S Response Surface Method to create design matrix in Minitab Using Minitab Software 5
S Minitab limit definition from R & R Using Minitab Software 4
G ANOVA GR&R: Minitab vs AIAG MSA results Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 7
S Standard Deviation Selection on Control Charts - Minitab "pooled deviation" Statistical Analysis Tools, Techniques and SPC 3
P Minitab Data Analysis - Understanding if a Process is in Control or Not Using Minitab Software 2
P Basic Minitab Help - It's about Linear Regression Using Minitab Software 1
A Taguchi Minitab - Continuous Data - What should I choose as response variable? Using Minitab Software 3

Similar threads

Top Bottom