Subscribe Bookmark RSS Feed

Formula in New Column Command

waltp

Community Trekker

Joined:

May 15, 2012

Hi,

I often receive data in SPSS format.  When I import the data into JMP, I maintain the SPSS names.  JMP creates a new column property called "SPSS Name" and also uses these names as the column names.  My goal is to change the column names based on "SPSS Name", add a new column with the new column name but with an added character in the name, and then have a formula for this new column.  As an example, suppose I use the Big Class table.  I added a new property to each column and called it "SPSS Name".  I then set each SPSS Name to "Q1", "Q2", ..., "Q5" which is along the lines of what the original SPSS file names would be like.  I created the following script which looks for the SPSS Name "Q4" (which corresponds to the height column in Big Class), changes the column name to "New Height", and adds a new column called "New Height - B".  This part works fine.  What I can't get to work is the formula part.  The new column should have a formula with a value based on the height column that was renamed; that is, based on "New Height".  When I look at the new column, all I see are missing value codes.  The formula property has the correct formula except that "x" is used rather than "New Height" as I would expect.  How can I get this last part to work?  Any suggestions?

dt = Current Data Table();

for(i = 1, i <= N Col( dt ), i++,

    col = Column( i );

    match( col << Get Property("SPSS NAME"),

        "Q4",

        x = "New Height";

        col << Set Name( x );

        New Column( x || " - B", Numeric, Continuous,

            Width( 5 ), Formula( If( Column( x ) > 60, 1, 0 ) )

        )

    );

);

dt << Run Formulas;

Thanks,

Walt Paczkowski

1 REPLY
bthaby1

Community Trekker

Joined:

Feb 23, 2012

The substitute command can be a tricky one and takes a while to grasp.

I created the SPSS property  of 'Q4' under the height column as you did.

dt = Current Data Table();

For(i = 1, i <= NCol(dt), i++,

  col = Column(i);

  Match(col << Get Property("SPSS NAME"),

       "Q4",

            newcoln = col << get name || " - B";

            newcol = dt << NewColumn(newcoln);

            // subsitute the values instead of the references

            Eval(Substitute(

            Expr(newcol << Set Formula(If( _xvar > 60, 1, 0))),

            Expr( _xvar ), Parse(col << Get Name))

       );

  );

);