74259_en-US

liliconcom

Transpose columns by variable name JMP14

I am attempting to transpose certain columns that all start with S (and only these columns start with S in my dataset-Sample dt attached).  Sometimes there are more S columns than others so I need to build a list of columns that start with S so I can pass that list to the transpose code.  Of course there are columns with other headers.

 

I see at least 2 other solved posts that are asking the same thing (link1 and link2) but I'm having trouble duplicating the results and I'm wondering if it is due to JMP14 not behaving the same or maybe i'm just doing it wrong?

 

My code is below but here is my error when I run it.  My sample dt is attached.

Substring: Not a String or Numeric in access or evaluation of 'AllCols[i]' , AllCols[/*###*/i]

In the following script, error marked by /*###*/
For( i = 1, i <= N Items( AllCols ), i++,
If(
Substr( AllCols[/*###*/i], 1, 1 ) == "S" &
Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
Insert Into( ColList, Num( Substr( AllCols[i], 2 ) ) )
)
)

 

  dt= current data table();
  AllCols = dt << get column names;
  ColList = {};
   For( i = 1, i <= N Items( AllCols ), i++,
    If(  Substr( AllCols[i], 1, 1 ) == "S" & Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
     Insert Into( ColList, Num( Substr( AllCols[i], 2 ) ) )
    );
   );

  // Create Column Lists
  For(i=1,i<=nItems(ColList),i++,
   ColList[i]=column("S" || char(ColList[i]));
  );

 

 

Re: Transpose columns by variable name JMP14

Jim, so it looks like one key thing i was missing was defining the column names as a string.  Thank you!

Re: Transpose columns by variable name JMP14

You were doing a lot more work than you needed to.  Here is a rework of your code

dt = Current Data Table();
AllCols = dt << get column names( string );
ColList = {};
For( i = 1, i <= N Items( AllCols ), i++,
 If( Substr( AllCols[i], 1, 1 ) == "S" & Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
  Insert Into( ColList, AllCols[i])
 )
);

dt2 = dt << Transpose(
 columns( eval( ColList )));