Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- Transpose columns by variable name JMP14

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 19, 2018 11:14 AM
(5085 views)

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]));
);
```

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

2 REPLIES 2

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Jim

Highlighted
##

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Transpose columns by variable name JMP14

Article Labels

There are no labels assigned to this post.