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

- JMP User Community
- :
- Discussions
- :
- Can matching data be obtained directly without auxiliary columns?

- 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

Aug 3, 2020 6:53 AM
(533 views)

For example, use the "age" column and "sex" column combinations of "Big class.jMP" to generate new column names on another table.

How to match the data of another table directly through "age" and "sex" column without adding auxiliary column in the big table?

Thanks!

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

Try this

```
names default to here(1);
// Create the 2 Example data tables
dtBig = open("$SAMPLE_DATA/big class.jmp");
dtSmall = New Table( "Data agesex",
Add Rows( 12 ),
New Column( "agesex",
Character,
"Nominal",
Set Values(
{"12F", "12M", "13F", "13M", "14F", "14M", "15F", "15M", "16F", "16M",
"17F", "17M"}
)
),
New Column( "N Rows",
Numeric,
"Continuous",
Format( "Fixed Dec", 12, 0 ),
Set Values( [5, 3, 3, 4, 5, 7, 2, 5, 2, 1, 1, 2] )
)
);
// Create the new column
dtBig << New Column( "N Rows" );
// Move the data as required
For( i = 1, i <= N Rows( dtSmall ), i++,
foundRows = dtBig << get rows where(
Num( Substr( dtSmall:agesex[i], 1, Length( dtSmall:agesex[i] ) - 1 ) ) == :age &
Substr( dtSmall:agesex[i], -1 ) == :sex
);
If( N Rows( foundRows ) > 0,
dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
);
);
```

Jim

7 REPLIES 7

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

Created:
Aug 3, 2020 8:17 AM
| Last Modified: Aug 3, 2020 8:18 AM
(520 views)
| Posted in reply to message from lwx228 08-03-2020

I'm not sure I completely understand what you are trying to do. As a specific example, are you trying to determine the number of 13 year old males in the data table and put that into a column without creating the age and gender concatenation column? If that is what you want, I think this formula column would work:

Note that this formula just counts the number of non-missing entries in the data table. In this case I picked to count the number of non-missing entries for the Name column by age and sex. No intermediate column or new table is needed.

Dan Obermiller

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

I haven't tested this code, but you should see at least the approach I am taking, and the approach will work.

```
Names Default To Here( 1 );
dtBig = Data Table( "Big Class" );
dtSmall = Data Table( "Big Class By (age, sex)" );
dt << New Column( "N Rows" );
For( i = 1, i <= N Rows( dtSmall ), i++,
foundRows = dtBig << get rows where(
Substr( :agesex[i], 1, Length( :agesex[i] ) - 1 ) == :age & Substr( agesex[i], -1 ) == :sex
);
If( N Rows( foundRows ) > 0,
dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
);
);
```

Jim

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

Thank Jim!

The code didn't run through.

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

Well, JMP can not find the small data table. From what I could see in your primary submission, the data table name for the small data table was called "Big Class by (age, sex)". Either that is not the correct name, or that data table no longer exists. Just change the names of the data table references to the table you want to run the script on and run it.

Jim

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

I have modified it several times, but still have no solution.

Go ahead and ask for your help.'Thank Jim!

Go ahead and ask for your help.'Thank Jim!

Highlighted

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

Try this

```
names default to here(1);
// Create the 2 Example data tables
dtBig = open("$SAMPLE_DATA/big class.jmp");
dtSmall = New Table( "Data agesex",
Add Rows( 12 ),
New Column( "agesex",
Character,
"Nominal",
Set Values(
{"12F", "12M", "13F", "13M", "14F", "14M", "15F", "15M", "16F", "16M",
"17F", "17M"}
)
),
New Column( "N Rows",
Numeric,
"Continuous",
Format( "Fixed Dec", 12, 0 ),
Set Values( [5, 3, 3, 4, 5, 7, 2, 5, 2, 1, 1, 2] )
)
);
// Create the new column
dtBig << New Column( "N Rows" );
// Move the data as required
For( i = 1, i <= N Rows( dtSmall ), i++,
foundRows = dtBig << get rows where(
Num( Substr( dtSmall:agesex[i], 1, Length( dtSmall:agesex[i] ) - 1 ) ) == :age &
Substr( dtSmall:agesex[i], -1 ) == :sex
);
If( N Rows( foundRows ) > 0,
dtBig:N Rows[foundRows] = dtSmall:N Rows[i]
);
);
```

Jim

Highlighted
##

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

Re: Can matching data be obtained directly without auxiliary columns?

Thank Jim!

There are no ready-made functions.

dtSmall

```
dtBig = Open( "$SAMPLE_DATA/big class.jmp" );
dtSmall = dtBig << Summary(
Group( :age, :sex ),
Freq( "None" ),
Weight( "None" ),
Link to original data table( 0 ),
statistics column name format( "column" )
);
c1 = Column( 1 ) << Get Name;
c2 = Column( 2 ) << Get Name;
dtSmall << Add Multiple Columns( c1 || c2, 1, after( As Column( 2 ) ), Character, "Nominal" );
Wait( 0 );
Column( 3 ) << Formula( Char( :age ) || :sex );
dtSmall << run formulas;
Column( 3 ) << deleteFormula;
dtSmall << delete columns( 1 :: 2 );
```