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

How to sum scores across multiple columns with condition?

helalmob — Sat, 10 Jun 2023 23:45:05 GMT

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

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

txnelson — Wed, 02 Mar 2022 14:40:07 GMT

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");

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

helalmob — Thu, 03 Mar 2022 16:40:40 GMT

Jim,

Thank you. Worked like a charm!

Helal