topic Re: column header search and replace in Discussions

column header search and replace

sam_t — Fri, 09 Jun 2023 22:10:25 GMT

Hi,

I can replace the column header name with the exact name using :height << Set Name( "test" );

If I have a list of text I want to replace to "".

How do I proceed with that?

"name" "Time" "sex" "height abc weight" "weight xyz" "12 height test"
KATIE 12 F 59 95 59
LOUISE 12 F 61 123 61

Final output:
"name" "Time" "sex" "abc" "xyz" "12 test"

Names Default To Here( 1 );
dt = Open( "C:/JSL/BigClass.jmp" );
headerList = dt << get column names( string );

//:height << Set Name( "test" );

For( i = 1, i <= N Items( headerList ), i++,
	If( (headerList[i] == "height" || headerList[i] == "weight"), //trying to find any column header has height or weight and replace with ""
		Column( dt, colList[i] ) << set name( " " ) //above are just two examples, maybe put it in list and loop?
	)
);

Re: column header search and replace

Thierry_S — Sat, 03 Apr 2021 05:04:08 GMT

Hi Sam,

I found 2 problems in your script:

You used the concatenate "||" code to represent your "Or" statement (should be "|")
JMP does not allow for column to be named as " ".

Also, it is usually faster and easier to call the column by number instead of iterating through the Column Name List

So, here is what I came up with:

Names Default To Here( 1 );
dt = Open( "C:\Program Files\SAS\JMP\14\Samples\Data\Big Class.jmp" );
headerList = dt << get column names( string );

For( i = 1, i <= N Items( headerList ), i++,
	If( (headerList[i] == "height" | headerList[i] == "weight"), //Fixed the "Or" statement
		Column( dt, i ) << set name(substitute (headerList [i], "e", "x")) //Used the substitute function as an example, you can replace any string with ""?
	)
);

Re: column header search and replace

sam_t — Sat, 03 Apr 2021 16:32:09 GMT

I tried to define the the list ahead of time and do double nested loop. It only substitute on stringReplace[3].

headerList = dt << get column names( string );
stringReplace = List();
stringReplace[1] = "height";
stringReplace[2] = "e";
stringReplace[3] = "a";

For( i = 1, i <= N Items( headerList ), i++,
	For( j = 1, j <= N Items( stringReplace ), j++,
		Column( dt, i ) << set name( Substitute(headerList[i], stringReplace[j], "" ) )
	);
);

Re: column header search and replace

Thierry_S — Sat, 03 Apr 2021 21:10:36 GMT

Hi Sam,

It seems that the issue is with the direct renaming of the column in the second loop (j) where you are still making substitution.

One possible solution (shown below) is to modify the list of headers (hl) and then to rename the column after all changes have been made:

Names Default To Here( 1 );
dt = Open( "C:\Program Files\SAS\JMP\14\Samples\Data\Big Class.jmp" );
hl = dt << get column names( string );

sr = List();
sr[1] = "height";									// 
sr[2] = "e";										// Order of replacement strings is critical
sr[3] = "a";										//	

For( i = 1, i <= N Items( hl ), i++,
	For (j = 1, j <= N Items (sr), j++,
		If (contains (hl [i], sr [j]), 
			txt = substitute (hl [i], sr [j], "x"); // Just for clarity: the expression could be wrapped into the next
			Substitute into (hl, hl [i], txt);		// Hold all the changes into the hl list
			)
		);
	Column (dt , i ) << set name (hl [i]);			// Renames column only after all changes have been made
);

I hope it helps.

Best,