nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Counting values across multiple columns

seoleelvjs

Hello,

I am primarily a SPSS user and new to the JMP, and I want to find a similar/identical SPSS function of "count values within cases" in JMP.

I wish to calculate the number of respondents who reported either one of "Not at all," (0) "Slightly," (1) "Somewhat," (2) "Moderatey," (3) and "Extremely" (4) across columns Know_1 to Know_3.

Then, I will create a separate column called "Know_total" that has total counts of respodents with each answer to yield a conclusion some thing like "35 of respondents replied "Moderately" in Knowledge assessment"

Because in SPSS, it's a two step process ( Count the number of certain values > Recode them into desirable ones in another column), and that is why I worded my question in such a way, but please educate me with a quicker option if any.

I do not care whether it's in JSL or not

Accepted answers

pmroz

You are referencing your dataset named "New" with a variable New. However the variable doesn't point to anything. This should fix things:

dt1 = data table("New");

dt2 = dt1 << Stack(
	columns( :Know_1, :Know_2, :Know_3 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

All comments

pmroz

Here's an approach that uses STACK, a formula column and TABULATE. I assumed that the original table has numbers for the answers.

 dt1 = New Table( "", Add Rows( 6 ),
	New Column( "Know_1", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [2, 1, 2, 3, 4, 2] )
	),
	New Column( "Know_2", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [2, 1, 2, 4, 2, 3] )
	),
	New Column( "Know_3", Numeric, "Ordinal", Format( "Best", 12 ),
		Set Values( [0, 1, 1, 1, 2, 4] )
	)
);

dt2 = dt1 << Stack(
	columns( :Know_1, :Know_2, :Know_3 ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" )
);

dt2 << New Column( "Value", Character, "Nominal", 
		Formula( 
			If(	:Data == 0, "Not at all",
				:Data == 1, "Slightly",
				:Data == 2, "Somewhat",
				:Data == 3, "Moderately",
				:Data == 4, "Extremely"
			)
		)
	);

dtab = dt2 << Tabulate(
	Show Control Panel( 0 ),
	Add Table( Row Table( Grouping Columns( :Value ) ) )
);

ian_jmp

You could also consider using the Categorical platform which is very functional. The code below reporoduces the example above:

NamesDefaultToHere(1);
// Test data with 'Value Labels'
dt = New Table( "Test",
				Add Rows( 6 ),
				New Column( "Know_1",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [2, 1, 2, 3, 4, 2] )
				),
				New Column( "Know_2",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [2, 1, 2, 4, 2, 3] )
				),
				New Column( "Know_3",
					Numeric,
					"Ordinal",
					Format( "Best", 12 ),
					Value Labels(
						{0 = "Not at all", 1 = "Slightly", 2 = "Somewhat", 3 = "Moderately", 4 = "Extremely"}
					),
					Use Value Labels( 1 ),
					Set Values( [0, 1, 1, 1, 2, 4] )
				)
			);
// Try 'Analyze > Consumer Research > Categorical' with 'Multiple Resoponses'
dt << Categorical( Multiple Response( :Know_1, :Know_2, :Know_3 ), Legend( 0 ) );

seoleelvjs

Hello pmroz and ian_jmp thank you for the prompt responses!
I have just one more question about the Set Values ([]).
I have total 3,465 rows and it would look like
dt1 = New Table( "", Add Rows( 3,465 ),
...?
Thus, I understand in the script you provided, Set values are the numerical values from row 1 to row 6, and with the change of row numbers, set values script would change as well,
but I do not get what it does...sorry I am not familar with JSL.

pmroz

We were just creating dummy tables, and adding values with the SET VALUES command. I expect your data is already present in your tables?

seoleelvjs

Hello, pmroz

Right, but then when I tried with the name of my dataset ("New"), the script didn't work (more like nothing happened T.T),

and I just assumed all sequences of scripts were needed somehow and perhaps the first sequence of the script had something to do with it...

Do you mind picking what did I do wrong here?

 dt2 <SPAN class="token operator">=</SPAN> New <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN> Stack<SPAN class="token punctuation">(</SPAN>
	columns<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Know_1<SPAN class="token punctuation">,</SPAN> <SPAN class="token punctuation">:</SPAN>Know_2<SPAN class="token punctuation">,</SPAN> <SPAN class="token punctuation">:</SPAN>Know_3 <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Source Label <SPAN class="token function">Column</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"Label"</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Stacked Data <SPAN class="token function">Column</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"Data"</SPAN> <SPAN class="token punctuation">)</SPAN>
<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

dt2 <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN> <SPAN class="token function">New Column</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"Value"</SPAN><SPAN class="token punctuation">,</SPAN> Character<SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Nominal"</SPAN><SPAN class="token punctuation">,</SPAN> 
		Formula<SPAN class="token punctuation">(</SPAN> 
			<SPAN class="token function">If</SPAN><SPAN class="token punctuation">(</SPAN>	<SPAN class="token punctuation">:</SPAN>Data <SPAN class="token operator">==</SPAN> <SPAN class="token number">0</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Not at all"</SPAN><SPAN class="token punctuation">,</SPAN>
				<SPAN class="token punctuation">:</SPAN>Data <SPAN class="token operator">==</SPAN> <SPAN class="token number">1</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Slightly"</SPAN><SPAN class="token punctuation">,</SPAN>
				<SPAN class="token punctuation">:</SPAN>Data <SPAN class="token operator">==</SPAN> <SPAN class="token number">2</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Somewhat"</SPAN><SPAN class="token punctuation">,</SPAN>
				<SPAN class="token punctuation">:</SPAN>Data <SPAN class="token operator">==</SPAN> <SPAN class="token number">3</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Moderately"</SPAN><SPAN class="token punctuation">,</SPAN>
				<SPAN class="token punctuation">:</SPAN>Data <SPAN class="token operator">==</SPAN> <SPAN class="token number">4</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token string">"Extremely"</SPAN>
			<SPAN class="token punctuation">)</SPAN>
		<SPAN class="token punctuation">)</SPAN>
	<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

dtab <SPAN class="token operator">=</SPAN> dt2 <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN> Tabulate<SPAN class="token punctuation">(</SPAN>
	<SPAN class="token function">Show</SPAN> Control Panel<SPAN class="token punctuation">(</SPAN> <SPAN class="token number">0</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	<SPAN class="token function">Add</SPAN> Table<SPAN class="token punctuation">(</SPAN> <SPAN class="token function">Row</SPAN> Table<SPAN class="token punctuation">(</SPAN> Grouping Columns<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Value <SPAN class="token punctuation">)</SPAN> <SPAN class="token punctuation">)</SPAN> <SPAN class="token punctuation">)</SPAN>
<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>

Thank you