Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level II

## How to replicate the same formula for other columns

Hi,

I have similar data as attached below. I need to create a column for each of the service variable with formula: Col Sum (Service 1, ID, Admission date). I have close to 100 different service variables, so doing this manually is out of question. Only the first argument I pass to Col Sum will change and by variables (ID and Admission date) remains the same for every formula. Is there a way to use JSL script to create columns and dynamically pass the variable to function and assign column name with the variable used to differentiate the source and output. Service1_1 will be one of the ouput column.

 ID Age Admission date Discharge date Order date Service 1 Service 2 Service 3 Service 1_1 12 25 13/2/2012 1/4/2012 1/3/2012 0 1 0 1 12 25 13/2/2012 1/4/2012 23/3/2012 1 1 1 1 13 17 27/8/2015 31/10/2015 3/9/2015 0 1 1 2 13 17 27/8/2015 31/10/2015 17/9/2015 1 1 0 2 13 17 27/8/2015 31/10/2015 28/10/2015 1 0 0 2 14 45 1/1/2016 13/2/2016 31/1/2016 0 0 1 1 14 45 1/1/2016 13/2/2016 8/2/2016 1 1 0 1
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: How to replicate the same formula for other columns

Here is a script that I think will help

``````Names Default To Here( 1 );
dt = Current Data Table();
nc = N Cols( dt );
For( i = 6, i <= nc, i++,
Eval(
Substitute(
Expr(
dt << New Column( __newname__,
formula( Col Sum( __oldname__, :ID, :Admission date ) )
)
),
Expr( __newname__ ), Column( i ) << get name || "_1",
Expr( __oldname__ ), Parse( ":" || Char( Column( dt, i ) << get name ) )
)
)
);``````

Additionally, you can do this interactively by using

Col==>New Columns

and specify as many new columns as you need.  Then, input the formula for the 1st of the new columns.  Finally, select that column, and all of the other new columns and then go to

Col==>Standardize Attributes

Select

Column Properties==>Formula

Click on the check box "Substitute Column Reference"

and then Click "OK"

It will copy the formula from the first new column, to all of the other columns, substituting the Service N column

Jim
2 REPLIES 2
Highlighted
Super User

## Re: How to replicate the same formula for other columns

Here is a script that I think will help

``````Names Default To Here( 1 );
dt = Current Data Table();
nc = N Cols( dt );
For( i = 6, i <= nc, i++,
Eval(
Substitute(
Expr(
dt << New Column( __newname__,
formula( Col Sum( __oldname__, :ID, :Admission date ) )
)
),
Expr( __newname__ ), Column( i ) << get name || "_1",
Expr( __oldname__ ), Parse( ":" || Char( Column( dt, i ) << get name ) )
)
)
);``````

Additionally, you can do this interactively by using

Col==>New Columns

and specify as many new columns as you need.  Then, input the formula for the 1st of the new columns.  Finally, select that column, and all of the other new columns and then go to

Col==>Standardize Attributes

Select

Column Properties==>Formula

Click on the check box "Substitute Column Reference"

and then Click "OK"

It will copy the formula from the first new column, to all of the other columns, substituting the Service N column

Jim
Highlighted
Level II

## Re: How to replicate the same formula for other columns

Thanks Jim. The solution worked well.
Article Labels

There are no labels assigned to this post.