cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
seoleelvjs
Level II

Counting values across multiple columns

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 =)

 

 

1.JPG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

Re: Counting values across multiple columns

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

View solution in original post

6 REPLIES 6
pmroz
Super User

Re: Counting values across multiple columns

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
Staff

Re: Counting values across multiple columns

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
Level II

Re: Counting values across multiple columns

 

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
Super User

Re: Counting values across multiple columns

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
Level II

Re: Counting values across multiple columns

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 = New << 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 ) ) )
); 

 

 

 

Thank you

pmroz
Super User

Re: Counting values across multiple columns

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