Subscribe Bookmark RSS Feed

Formula usage: How to create second column based on retained values from first column?

poulravn

Community Trekker

Joined:

Jan 25, 2012

Hi, a recurring tasks can be seen in the attached JMP data table example. A first column has row values, that need to be continued for more rows than where the first column has the value. In thye example the first column has the numbers 1, 2, 3 etc mixed with letters (or any other irrevelant information). Now, the value of the second column should always be equal to the last value encountered in the first column. When a new number is seen in the first column, that value should be continued. Sounds complicated, but looks simpler in the table.

Any help appreciated. (In base SAS retain would do the job.)

Regards

Poul

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

If you need a formula column, I think this will work (assumes first row in Column 1 is a number):

New Column( "test",

  Numeric,

  Continuous,

  Formula(

  If( Is Missing( Num( :Column 1 ) ),

  Lag( :test, 1 ),

  Num( :Column 1 )

  )

  )

);

1 REPLY
Solution

If you need a formula column, I think this will work (assumes first row in Column 1 is a number):

New Column( "test",

  Numeric,

  Continuous,

  Formula(

  If( Is Missing( Num( :Column 1 ) ),

  Lag( :test, 1 ),

  Num( :Column 1 )

  )

  )

);