cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
helalmob
Level II

How to sum scores across multiple columns with condition?

I have a data set that includes 1600 subjects and more than 100 variables/questions. I need to calculate the total score across the 33 question items (Q1 thru Q33) ranging from 0 to 4 for each subject where the score of each question >= 2 excluding 0 and 1. Any help/suggestions/directions are greatly appreciated.

 

Thank you

 

Hella

 

PS. I am using JMP Pro 16.2

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to sum scores across multiple columns with condition?

Here is one way to calculate the values.  It is an efficient way to calculate the final score.  

Names Default To Here(1);

dt = current data table();

dt<<new column("theRow",set each value(row()));

dtStack = dt << Stack(invisible,
	columns(
		:Q1, :Q2, :Q3, :Q4, :Q5, :Q6, :Q7, :Q8, :Q9, :Q10, :Q11, :Q12, :Q13, :Q14,
		:Q15, :Q16, :Q17, :Q18, :Q19, :Q20, :Q21, :Q22, :Q23, :Q24, :Q25, :Q26, :Q27,
		:Q28, :Q29, :Q30, :Q31, :Q32, :Q33
	),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	"Non-stacked columns"n( Keep( :theRow ) )
);

dtStack << select where( :Data <2 );
dtStack << delete rows;

dtSum = dtStack << Summary(invisible,
	Group( :theRow ),
	Sum( :Data ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to Original data Table(0)
);

close( dtStack, nosave );

dtSum << delete columns("N Rows");
dtSum:Data << Set Name("Score");

dt << Update(
	With( dtSum ),
	Match Columns( :theRow = :theRow )
);

close( dtSum, nosave );

dt << delete columns("theRow");
Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: How to sum scores across multiple columns with condition?

Here is one way to calculate the values.  It is an efficient way to calculate the final score.  

Names Default To Here(1);

dt = current data table();

dt<<new column("theRow",set each value(row()));

dtStack = dt << Stack(invisible,
	columns(
		:Q1, :Q2, :Q3, :Q4, :Q5, :Q6, :Q7, :Q8, :Q9, :Q10, :Q11, :Q12, :Q13, :Q14,
		:Q15, :Q16, :Q17, :Q18, :Q19, :Q20, :Q21, :Q22, :Q23, :Q24, :Q25, :Q26, :Q27,
		:Q28, :Q29, :Q30, :Q31, :Q32, :Q33
	),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	"Non-stacked columns"n( Keep( :theRow ) )
);

dtStack << select where( :Data <2 );
dtStack << delete rows;

dtSum = dtStack << Summary(invisible,
	Group( :theRow ),
	Sum( :Data ),
	Freq( "None" ),
	Weight( "None" ),
	statistics column name format( "column" ),
	Link to Original data Table(0)
);

close( dtStack, nosave );

dtSum << delete columns("N Rows");
dtSum:Data << Set Name("Score");

dt << Update(
	With( dtSum ),
	Match Columns( :theRow = :theRow )
);

close( dtSum, nosave );

dt << delete columns("theRow");
Jim
helalmob
Level II

Re: How to sum scores across multiple columns with condition?

Jim,

Thank you. Worked like a charm!

 

Helal