Data Manipulation (Minitab)

Padding columns, creating a column of row numbers, etc.

Here are links to the code outlined in the comments below (with proper indenting) – you can copy & paste them into a text editor (such as Notepad) to create macros:

Pad_Rows

Pad_Rows_ex_Blanks

 

4 thoughts on “Data Manipulation (Minitab)

  1. PAD ROWS
    This Minitab macro cleans up a worksheet so that all columns are the same
    length. Often when you read in data some columns may have missing data on
    the last rows. The unequal column lengths stops you from graphing the 2
    columns and doing other analyses.
    =============================================================================

    gmacro
    Pad_Rows

    # Find longest column

    Let k700 = 0
    DO k701 = 1 : 9999
    Dtype ck701 k702 # Column Type 0=text; 1=real #; 2=integers; 3=date/time; 10=empty
    if (k702 = 10) # Stop when encounter first blank column
    Break # Exit loop
    endif

    # if this column is longer, update length of longest column constant

    let k700 = if(count(ck701)>k700,count(ck701),k700)

    enddo

    # Pad columns until encounter first blank column

    DO k701 = 1 : 9999
    DType ck701 k702 # Column Type 0=text; 1=real; 2=integer; 3=date/time; 10=empty
    if (k702 = 10) # Stop when encounter first blank column
    Break # Exit loop
    endif

    if(count(ck701)

  2. ROW NUMBER
    This code example creates a column “RowNum” with first row’s value as 1,
    the second’s as 2, etc. The advantage of this approach vs creating
    patterned data is that you don’t have to worry about the length of the
    column – it will automatically be created as the same length as your
    “test” column – C1 in this case.
    ========================================================================

      # Create row number variable using partial sum tested against a value that is not found
    
        Name c19 "RowNum"
        Let 'RowNum' = pars(c1<>-9999999.999)   # C1 is a numeric column

  3. PAD ROWS – worksheet with embedded blank columns

    This Minitab macro cleans up a worksheet so that all columns are the same
    length. Often when you read in data some columns may have missing data on
    the last rows. The unequal column lengths stops you from graphing the 2
    columns and doing other analyses. This more complex macro handles the case of blank columns within the worksheet. By default it will look ahead and process the column unless the next 6 columns are empty.

    gmacro
    Pad_Rows_ex_Blanks

    Let k710 = 5 # k710 + 1 is Number of columns to look ahead for non blank column

    # Find longest column, number of columns to process

    Let k700 = 0
    DO k701 = 1 : 9999 # Last value of k701 – 1 will be number of columns to process
    Dtype ck701 k702 # Column Type 0=text; 1=real #; 2=integers; 3=date/time; 10=empty
    Let k704 = 1 # Number of blank columns in a row
    if (k702 = 10) # Empty column
    DO k711 = 1 : k710 # Look ahead k710 columns for non Empty column
    Let k703 = k701 + k711 # Column to check
    Dtype ck703 k702 # Column Type 0=text; 1=real #; 2=integers; 3=date/time; 10=empty
    if (k702 = 10) # blank column
    Let k704 = k704 + 1 # Increment count of blank columns
    endif
    if k704 > 5
    Break # Exit inner loop
    Endif
    EndDo
    else # non empty column
    # if this column is longer, update length of longest column constant
    let k700 = if(count(ck701)>k700,count(ck701),k700)
    EndIf
    if k704 > 5
    Break # Exit outer loop
    Endif
    enddo

    # Pad columns

    Let k701 = k701 – 1 # Previous loop counter stopped on empty column – back up 1

    DO k721 = 1 : k701
    DType ck721 k702 # Column Type 0=text; 1=real; 2=integer; 3=date/time; 10=empty
    if (k702 < 10) # non Empty column if(count(ck721)

  4. Hi,

    I have several worksheet with data columns and a final columns with some result.
    Is there a way to make copy copy of these result columns in a new worksheet as grand view.
    Do you think is possible with a macro?

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *