cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
See how to use to use Text Explorer to glean valuable information from text data at April 25 webinar.
Choose Language Hide Translation Bar
View Original Published Thread

counting character columns in jsl

ERobison
Level I

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)

0112

1

113
1001
0101
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User


Re: counting character columns in jsl

@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 )
);
Jim

View solution in original post

3 REPLIES 3


Re: counting character columns in jsl

A formula is all you need.

 

formula.PNG

 

Here is the result.

 

table.PNG

ERobison
Level I


Re: counting character columns in jsl

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!

txnelson
Super User


Re: counting character columns in jsl

@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 )
);
Jim