turn on suggestions

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

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Iterate through multiple columns and replace cells

Topic Options

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

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

Jun 13, 2017 1:16 PM
(1112 views)

Hello everyone,

I'm trying to iterate through multiple tables and replace any value >6 sigma with a ".". Below is my attempt to iterate through big class and replace the weight and height values greater than 1 sigma with a .; however, it is not working as intended. I think I'm confusing myself with the two strings.

```
Names Default To Here( 1 );
Clear Globals();
dt = Open("...\SAS\JMP\13\Samples\Data\Big Class.jmp");
mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );
For( i = 1, i <= N Items( list_col_names ), i++,
cur_col = (list_col_names[i]);
If( Left( cur_col,1 ) == "ei",
mycol = dt << get as matrix( cur_col );
q25 = Quantile( 0.25, mycol );
q50 = Quantile( 0.50, mycol );
q75 = Quantile( 0.75, mycol );
mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
For( r = 1, r <= N Rows( dt ), r++,
If(
mycol1[r] > 1, mycol1[r] = .,
mycol1[r] < -1, mycol1[r] = .
));
);
);
```

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions

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

Jun 13, 2017 1:59 PM
(2209 views)

Solution

The main issues that I am seeing are that you did not identify the Hieght and Weight columns and that once you found the rows above or below 1 sigma, the values were not written back to the data table.

Here is a modification to your code that corrects those issues.

```
Names Default To Here( 1 );
Clear Globals();
dt = Open("$SAMPLE_DATA\Big Class.jmp");
mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );
For( i = 4, i <= N Items( list_col_names ), i++,
cur_col = (list_col_names[i]);
If( Contains(cur_col,"ei") > 0,
mycol = dt << get as matrix( cur_col );
q25 = Quantile( 0.25, mycol );
q50 = Quantile( 0.50, mycol );
q75 = Quantile( 0.75, mycol );
mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
For( r = 1, r <= N Rows( dt ), r++,
If(
mycol1[r] > 1, column(dt,cur_col)[r] = .,
mycol1[r] < -1, column(dt,cur_col)[r] = .
));
);
);
```

Jim

2 REPLIES

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

Jun 13, 2017 1:59 PM
(2210 views)

The main issues that I am seeing are that you did not identify the Hieght and Weight columns and that once you found the rows above or below 1 sigma, the values were not written back to the data table.

Here is a modification to your code that corrects those issues.

```
Names Default To Here( 1 );
Clear Globals();
dt = Open("$SAMPLE_DATA\Big Class.jmp");
mycol = J( N Rows( dt ), 1 );
list_col_names = dt << Get Column Names( String );
For( i = 4, i <= N Items( list_col_names ), i++,
cur_col = (list_col_names[i]);
If( Contains(cur_col,"ei") > 0,
mycol = dt << get as matrix( cur_col );
q25 = Quantile( 0.25, mycol );
q50 = Quantile( 0.50, mycol );
q75 = Quantile( 0.75, mycol );
mycol1 = ((mycol - q50) / ((q75 - q25) / 1.35));
For( r = 1, r <= N Rows( dt ), r++,
If(
mycol1[r] > 1, column(dt,cur_col)[r] = .,
mycol1[r] < -1, column(dt,cur_col)[r] = .
));
);
);
```

Jim

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

Jun 13, 2017 2:05 PM
(1101 views)

Thats where my issue was, it works great! Thank you for your help!