nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Join equal jmp datasets by loop (scripting)

Newbie2Jumpie

I have 100 structurally identical datasets. All of them contain an ID and a constant/field, that contains a name of that dataset.

To concatenate them (format "long") is easy-peasy.

Now I need to merge them into format "wide", is that equally easy?

I think of using that constant as a prefix for each JMP datasets, but how could I make that work in a loop.

Have any suggestions?

Thank you very much and Kind regards

Newbie

Accepted answers

txnelson

I am not exactly clear on what you want your output data table to look like, however, here is a simple example that will merge data tables, similar to the structure your discription is

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( "A" || Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	current data table(dt);
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	dt2 << New Column("Table Name", character, set each value(dt2<<get name))
);
wait(0);
close(dt,nosave);


// Merge the tables together

// Get the table Names
tableList = {};
for(i=1,i<=N Table(), i++,
	insert into(tableList, data table(i)<<get name);
);

// Loop across the tables and using Update put them back together
For( i = 2, i <= N Items( tableList ), i++,
	Data Table( tableList[1] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) )
);

All comments

txnelson

I am not exactly clear on what you want your output data table to look like, however, here is a simple example that will merge data tables, similar to the structure your discription is

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( "A" || Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	current data table(dt);
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	dt2 << New Column("Table Name", character, set each value(dt2<<get name))
);
wait(0);
close(dt,nosave);


// Merge the tables together

// Get the table Names
tableList = {};
for(i=1,i<=N Table(), i++,
	insert into(tableList, data table(i)<<get name);
);

// Loop across the tables and using Update put them back together
For( i = 2, i <= N Items( tableList ), i++,
	Data Table( tableList[1] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) )
);

Newbie2Jumpie

Hi Tx,

I don't have access to JMP at the momement (I'm travelling) but the result should look like this.

Now, looking at the structure myself, I think concatentating, then flipping/looping the contatenated table may be a straightforward approach (the IDs are not that important). However, I would still like to learn whether this could be solved by a simplest (macro) loop.

File1:

ID Constant Value

1 AB 123

2 AB 456

File2:

ID Constant Value

1 DEF 123

2 DEF 456

...

File100:

ID Constant Value

1 Z 123

2 Z 456

"Loop" By CONSTANT

Desired result

AB_ID AB_Constant AB_Value DEF_ID DEF_ Constant DEF_Value ... Z_ID Z_ Constant Z_Value

1 AB 123 1 DEF 123 ... 1 Z 123

2 AB 456 2 DEF 456 ... 2 Z 123

Newbie2Jumpie

For whatever reason my post from Friday afternoon seemd to get lost...

That's the price you pay when you work an a train, or so it seems.

I have 100 datasets, each contains a different constant :

e.g.

Dataset1

ID CONSTANT VALUE1 VALUE2

1 A 123 123

2 A 123 123

Dataset2

1 B 456 456

2 B 456 456

Dataset3

1 C 789 789

2 C 789 789

all I want is to get them into a single dataset with the following structure:

A_ID A_CONSTANT A_VALUE1 A_VALUE2 B_ID B_CONSTANT B_VALUE1 B_VALUE2

1 A 123 123 1 B 456 456

2 A 123 123 2 B 456 456 etc,

I see two ways to get there:

(1) add a prefix to each single column within each dataset first and then merge them by ID, or

(2) contatente all the datasets first and then transpose/flip/loop them using the constant levels as a prefix (however, I found that JMP doesn´t like mixed datatypes)

Any idea how to get there... ?

Any help greatly appreciated

Newbie

txnelson

Here is a rework of my original script, taking into account the new information you provided.

You also might want to look into using your contatenated data table, and using

Tables==>Split

It can get you very close to what you want

Names Default To Here( 1 );

// Create example data tables to be used in the merging
dt = Open( "$SAMPLE_DATA/Blood Pressure.jmp" );

constants = "ABCDEFGHIJKLMNOP";

dt << delete columns( {"Subject", "Dose"} );
dt << New Column( "ID", character, formula( Char( Row() ) ) );
dt << Go To( :ID );
dt << Move Selected Columns( To First );
For( i = 2, i <= N Cols( dt ), i++,
	Current Data Table( dt );
	dt2 = dt << subset( selected rows( 0 ), columns( :ID, Column( i ) ) );
	Column( dt2, 2 ) << set name( "Value1" );
	dt2 << New Column( "Value2", formula( Floor( :Name( "Value1" ) + Random Integer( 5 ) ) ) );
	dt2 << New Column( "Constant", character, set each value( Substr( constants, i - 1, 1 ) ) );
);
Wait( 0 );
Close( dt, nosave );


// Merge the tables together

// Get the table Names
tableList = {};
For( i = 1, i <= N Table(), i++,
	Insert Into( tableList, Data Table( i ) << get name )
);
theConstant = Column( Data Table( tableList[N Items( tableList )] ), "Constant" )[1];
Data Table( tableList[N Items( tableList )] ) << New Column( theConstant || "_ID", character, formula( :ID ) );
Column( Data Table( tableList[N Items( tableList )] ), "Value1" ) << set name( theConstant || "_Value1" );
Column( Data Table( tableList[N Items( tableList )] ), "Value2" ) << set name( theConstant || "_Value2" );

// Loop across the tables and using Update put them back together
For( i = N Items( tableList ) - 1, i >= 1, i--,
	theConstant = Column( Data Table( tableList[i] ), "Constant" )[1];
	Data Table( tableList[i] ) << New Column( theConstant || "_ID", character, formula( :ID ) );
	Column( Data Table( tableList[i] ), "Value1" ) << set name( theConstant || "_Value1" );
	Column( Data Table( tableList[i] ), "Value2" ) << set name( theConstant || "_Value2" );
	Data Table( tableList[N Items( tableList )] ) << Update( With( Data Table( tableList[i] ) ), Match Columns( :ID = :ID ) );
);

Mark_Bailey

I cannot imagine what the data sets represent or what the resulting combined data set is for. That information might help us solve your problem better. If the data sets are identical in structure and contain only repeating constants, then it seems to me that there are more efficient data structures and data methods for your problem.

So what are you going to do in JMP with the combined data set?