topic Re: Recode multiple values in multiple columns in Discussions

Recode multiple values in multiple columns

MattP — Thu, 13 Dec 2018 16:20:47 GMT

Hello JMP Experts,

I am tying to recode 103 columns of data in a data table. There are quite a few recodes that need to happen in each column. I was trying to follow this post:

https://community.jmp.com/t5/Discussions/Recode-multiple-columns-with-JSL-script/m-p/7865#M7859

but use recode instead of "if".

I think my problem is with the "cols" I used. I'm unsure how to reference the column I am targeting and move from one column to the next with each loop inside of the "for".

Thank you for your help!

cols = dtconcat << get column names;
For Each Row( For(i=1, i<=N Items(cols), i++,
	cols(i) = Match( cols(i),
		"NQ", "0",
		"Nq", "0",
		"NA", ".",
		"N/A", ".",
		">30", "30",
		">20", "20",
		"<5", "0",
		"<2.00", "0",
		"<2.0", "0",
		"<2", "0",
		"<1", "0",
		"<0.5", "0",
		"<0.01", "0",
		"#N/A", ".",
		"-", ".",
		cols(i))
	)
);

Re: Recode multiple values in multiple columns

txnelson — Thu, 13 Dec 2018 18:46:11 GMT

You have 2 issues.

The proper syntax for using a subscript is to inclose it in "[ ]" not "( )".
You need to tell JMP that Cols[i] is to be used as a column reference

See the corrected script below

cols = dtconcat << get column names;
For Each Row(
	For( i = 1, i <= N Items( cols ), i++,
		As Column( cols[i] ) =
		Match( As Column( cols[i] ),
			"NQ", "0",
			"Nq", "0",
			"NA", ".",
			"N/A", ".",
			">30", "30",
			">20", "20",
			"<5", "0",
			"<2.00", "0",
			"<2.0", "0",
			"<2", "0",
			"<1", "0",
			"<0.5", "0",
			"<0.01", "0",
			"#N/A", ".",
			"-", "."
		)
	)
);

Re: Recode multiple values in multiple columns

MattP — Thu, 13 Dec 2018 19:11:33 GMT

Thank you very much!