Choose Language Hide Translation Bar
poulravn
Community Trekker

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

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
Highlighted
ms
Super User ms
Super User

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

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 )

  )

  )

);

View solution in original post

0 Kudos
1 REPLY 1
Highlighted
ms
Super User ms
Super User

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

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 )

  )

  )

);

View solution in original post

0 Kudos