I have a data table with several data columns with binary 0/1 responses (character, nominal). I want to create a new column (numerical, continuous) where I am counting the instances of "1"s in these columns.
can someone help me create a script to create this column? I feel like this should be easy but I am running into road blocks. Do I have to convert my character columns to numerical/continuous columns and do summation formula? Thank you!
Example table
| infection (character, nominal) | blood clot (character, nominal) | surgery (character, nominal) | NEW COLUMN: number of complications (numerical, continuous) |
| 0 | 1 | 1 | 2 |
1 | 1 | 1 | 3 |
| 1 | 0 | 0 | 1 |
| 0 | 1 | 0 | 1 |
@Mark_Bailey provided a good solution
Here is another way to do solve the problem
Names Default To Here( 1 );
dt = Current Data Table();
charNames = dt << get column names( character );
// convert char columns to numeric
For( i = 1, i <= N Items( charNames ), i++,
Column( dt, charNames[i] ) << data type( numeric )
);
// Create the jsl required to sum all of the specified columns
theExpr = "dt << new column(\!"number of complications\!", formula(sum(" ||
Substr( Char( charnames ), 2, Length( Char( charNames ) ) - 2 ) || "));";
// Execute the line of code created
Eval( Parse() );
// Delete the formula leaving the static values
Column( dt, "number of complications" ) << delete formula;
// Convert the binary columns back to character
For( i = 1, i <= N Items( charNames ), i++,
Column( dt, charNames[i] ) << data type( character )
);
A formula is all you need.
Here is the result.
Thanks to both @Mark_Bailey and @txnelson for providing two approaches to my problem. I am going to go with the script as I am trying to create a full script for my data table creation to be used again in the future.
thank you!
@Mark_Bailey provided a good solution
Here is another way to do solve the problem
Names Default To Here( 1 );
dt = Current Data Table();
charNames = dt << get column names( character );
// convert char columns to numeric
For( i = 1, i <= N Items( charNames ), i++,
Column( dt, charNames[i] ) << data type( numeric )
);
// Create the jsl required to sum all of the specified columns
theExpr = "dt << new column(\!"number of complications\!", formula(sum(" ||
Substr( Char( charnames ), 2, Length( Char( charNames ) ) - 2 ) || "));";
// Execute the line of code created
Eval( Parse() );
// Delete the formula leaving the static values
Column( dt, "number of complications" ) << delete formula;
// Convert the binary columns back to character
For( i = 1, i <= N Items( charNames ), i++,
Column( dt, charNames[i] ) << data type( character )
);