Re: batching recoding of many columns

Sburel · Jun 9, 2023 3:08 PM

Hello,

I've been trying to recode many columns which contain a specific string in their column name using the recode function. I think I'm pretty close but the script is getting stuck with the column evaluations. So I'm missing something crucial somewhere. Any guidance would be greatly appreciated.

Best,

Sebastien

Names default to here(1);
Clear log();

New Table( "Subject_Demographics",
	Add Rows( 48 ),
	New Column( "Unique Subject Identifier",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"8396882-P0101", "8396882-P0101", "8396882-P0102", "8396882-P0102",
			"8396882-P0103", "8396882-P0103", "8396882-P0104", "8396882-P0104",
			"8396882-P0201", "8396882-P0201", "8396882-P0202", "8396882-P0202",
			"8396882-P0203", "8396882-P0203", "8396882-P0204", "8396882-P0204",
			"8396882-P0301", "8396882-P0301", "8396882-P0302", "8396882-P0302",
			"8396882-P0303", "8396882-P0303", "8396882-P0304", "8396882-P0304",
			"8396882-P0401", "8396882-P0401", "8396882-P0402", "8396882-P0402",
			"8396882-P0403", "8396882-P0403", "8396882-P0404", "8396882-P0404",
			"8396882-P0501", "8396882-P0501", "8396882-P0502", "8396882-P0502",
			"8396882-P0503", "8396882-P0503", "8396882-P0504", "8396882-P0504",
			"8396882-P0601", "8396882-P0601", "8396882-P0602", "8396882-P0602",
			"8396882-P0603", "8396882-P0603", "8396882-P0604", "8396882-P0604"}
		)
	),
	New Column( "Alertness - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"", "", "", "", "", "", "", "", "", "", "Abnormal Finding",
			"Abnormal Finding", "", "", "", "", "", "", "", "", "", "", "", "", "",
			"", "Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding", "", "", "",
			"", "Abnormal Finding", "Abnormal Finding", "", "", "", "", "", "", "",
			"", "", ""}
		)
	),
	New Column( "Xyx Reflex - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Normal", "Normal", "", "", "", "", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Abnormal Finding", "Abnormal Finding", "Normal", "Normal",
			"Normal", "Normal", "", "", "", "", "Abnormal Finding",
			"Abnormal Finding", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal"}
		)
	),
	New Column( "Ataxia - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "", "", "Abnormal Finding",
			"Abnormal Finding", "", "", "", "", "", "", "", "", "", "", "", "",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "", "", "Abnormal Finding", "Abnormal Finding", "",
			"", "", "", "Abnormal Finding", "Abnormal Finding", "", "", "", "", "",
			"", "", "", "Abnormal Finding", "Abnormal Finding"}
		)
	)
);


subject_incidence = Data Table( "Subject_Demographics" );
a = "Recoded";
col = subject_incidence << get column names( string );
show(col);
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], a ), 	
		subject_incidence << Begin Data Update;
		subject_incidence << Recode Column(
			column(subject_incidence, col[i]),
			{Map Value( _rcOrig, {"", "Not Reported"}, Unmatched( _rcNow ) )},
			Target Column( column(subject_incidence, col[i] ))
		);
		subject_incidence << End Data Update
	)
);

ih · Mar 15, 2021 10:06 PM

Inside the recode column function, JMP doesn't know about the variable col anymore, so you need to evaluate that part of the script before the recode column evaluates. To do that, you can use the Eval, Eval Expr, and Expr functions, like this:

Names default to here(1);

dtSD = New Table( "Subject_Demographics",
	Add Rows( 48 ),
	New Column( "Unique Subject Identifier",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"8396882-P0101", "8396882-P0101", "8396882-P0102", "8396882-P0102",
			"8396882-P0103", "8396882-P0103", "8396882-P0104", "8396882-P0104",
			"8396882-P0201", "8396882-P0201", "8396882-P0202", "8396882-P0202",
			"8396882-P0203", "8396882-P0203", "8396882-P0204", "8396882-P0204",
			"8396882-P0301", "8396882-P0301", "8396882-P0302", "8396882-P0302",
			"8396882-P0303", "8396882-P0303", "8396882-P0304", "8396882-P0304",
			"8396882-P0401", "8396882-P0401", "8396882-P0402", "8396882-P0402",
			"8396882-P0403", "8396882-P0403", "8396882-P0404", "8396882-P0404",
			"8396882-P0501", "8396882-P0501", "8396882-P0502", "8396882-P0502",
			"8396882-P0503", "8396882-P0503", "8396882-P0504", "8396882-P0504",
			"8396882-P0601", "8396882-P0601", "8396882-P0602", "8396882-P0602",
			"8396882-P0603", "8396882-P0603", "8396882-P0604", "8396882-P0604"}
		)
	),
	New Column( "Alertness - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"", "", "", "", "", "", "", "", "", "", "Abnormal Finding",
			"Abnormal Finding", "", "", "", "", "", "", "", "", "", "", "", "", "",
			"", "Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding", "", "", "",
			"", "Abnormal Finding", "Abnormal Finding", "", "", "", "", "", "", "",
			"", "", ""}
		)
	),
	New Column( "Xyx Reflex - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Normal", "Normal", "", "", "", "", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Abnormal Finding", "Abnormal Finding", "Normal", "Normal",
			"Normal", "Normal", "", "", "", "", "Abnormal Finding",
			"Abnormal Finding", "Normal", "Normal", "Normal", "Normal", "Normal",
			"Normal", "Normal", "Normal", "Normal", "Normal"}
		)
	),
	New Column( "Ataxia - Recoded",
		Character,
		"Nominal",
		Set Selected,
		Set Values(
			{"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "Abnormal Finding", "", "", "Abnormal Finding",
			"Abnormal Finding", "", "", "", "", "", "", "", "", "", "", "", "",
			"Abnormal Finding", "Abnormal Finding", "Abnormal Finding",
			"Abnormal Finding", "", "", "Abnormal Finding", "Abnormal Finding", "",
			"", "", "", "Abnormal Finding", "Abnormal Finding", "", "", "", "", "",
			"", "", "", "Abnormal Finding", "Abnormal Finding"}
		)
	)
);


a = "Recoded";
col = dtSD << get column names( );

dtSD << Begin Data Update;
For( i = 1, i <= N Items( col ), i++,
	If( Contains( col[i], a ), 	
		
		//Need to evaluate the name of the column before 
		e = Eval Expr( 
			Expr( dtSD ) << Recode Column(
				As Column( Expr( col[i] ) ),
				{Map Value( _rcOrig, {"", "Not Reported"}, Unmatched( _rcNow ) )},
				Target Column( As Column( Expr( col[i] ) ) )
			);
		);
		show(e); write("\!n");
		eval(e);
	)
);

dtSD << End Data Update

Note that I saved the expression as e so it could be written to the log before being evaluated. You could have put Eval( Eval Expr( on the same line.

There are many ways to construct this code, here are a few examples:

Insert one expression into another using Eval Insert, Eval Expr, Parse, and Substitute

txnelson · Mar 16, 2021 12:21 AM

I believe the code can be a little simpler than @ih

Your code with just a change to using an As Column() function rather than just a Column() function allows the code to run correctly

subject_incidence = Data Table( "Subject_Demographics" );
a = "Recoded";
col = subject_incidence << get column names( string );
show(col);
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], a ), 	
		subject_incidence << Begin Data Update;
		subject_incidence << Recode Column(
			as column(subject_incidence, col[i]),
			{Map Value( _rcOrig, {"", "Not Reported"}, Unmatched( _rcNow ) )},
			Target Column( column(subject_incidence, col[i] ))
		);
		subject_incidence << End Data Update
	)
);

Here is also a different approach to solve your issue by using Get Rows Where() to find the rows that have no value, and changing those rows value to Not Reported

subject_incidence = Data Table( "Subject_Demographics" );
a = "Recoded";
col = subject_incidence << get column names( string );
Show( col );
nc = N Items( col );
For( i = 1, i <= nc, i++,
	If( Contains( col[i], a ),
		Try(
			Column( subject_incidence, col[i] )[subject_incidence <<
			get rows where( as Column( subject_incidence, col[i] ) == "" )] = "Not Reported"
		)
	)
);

Jim