Subscribe Bookmark RSS Feed

Counting values across multiple columns

seoleelvjs

New Contributor

Joined:

Aug 21, 2017

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

Joined:

Jun 23, 2011

Solution

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" )
);
6 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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

Joined:

Jun 23, 2011

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

New Contributor

Joined:

Aug 21, 2017

 

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

Joined:

Jun 23, 2011

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

New Contributor

Joined:

Aug 21, 2017

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

Joined:

Jun 23, 2011

Solution

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