topic Can this simple two-step operation be done in one step? in Discussions

Can this simple two-step operation be done in one step?

lala — Wed, 10 Apr 2024 06:17:51 GMT

Can it be done with memory arrays?

Thanks!

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
ca = "sex";
dt << Select Where( Row() < 25 );
dt << Select Columns( ca );
d3 = dt << Subset( Output Table( "b3" ), Selected Rows( 1 ), columns( ca ) );
d2 = d3 << Summary(
	Group( 1 ),
	Freq( 0 ),
	Weight( 0 ),
	Link to original data table( 0 ),
	Output Table( "b2" )
);

Is there no need to generate d3?

Re: Can this simple two-step operation be done in one step?

jthi — Wed, 10 Apr 2024 07:01:08 GMT

What do you want to do?

Edit: create summary table?

Re: Can this simple two-step operation be done in one step?

lala — Wed, 10 Apr 2024 08:29:01 GMT

I want to extract a subset of the data in this column, and then aggregate the respective numbers of these subsets.Note that the format of this column is text.

Thanks!

Re: Can this simple two-step operation be done in one step?

jthi — Wed, 10 Apr 2024 08:50:50 GMT

Do you want them in table? Do you need the subset (you mentioned you want to "extract a subset")?

Re: Can this simple two-step operation be done in one step?

lala — Wed, 10 Apr 2024 09:51:24 GMT

Subsets may not appear.However, the final breakdown needs to be presented as a table of data.

Thanks!

Re: Can this simple two-step operation be done in one step?

jthi — Wed, 10 Apr 2024 09:55:55 GMT

Here are few options, but it is very easy to come up with even more (also I'm not sure what would be the issue of using subset as you can create it as private and close it immediately after you have the subset)

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
groupcol = "sex";

// Filter
dt_filter = dt << Summary(
	Local Data Filter(
		Add Filter(
			columns(Transform Column("Row", Formula(Row()))),
			Where(Transform Column("Row", Formula(Row())) < 25)
		)
	),
	Group(Eval(groupcol)),
	Freq("None"),
	Weight("None"),
	Link to original data table(0),
	Output table("1")
);

// Exclude
dt << Select Where(Row() >= 25) << Hide and Exclude(1);
dt_exclude = dt << Summary(
	Group(Eval(groupcol)),
	Freq("None"),
	Weight("None"),
	Link to original data table(0),
	Output table("2")
);
dt << Clear Row States;

// SQL
dt_sql = Query(
	Table(dt, "bc"),
	Eval Insert("\[
	SELECT ^groupcol^, COUNT(rowid) "N Rows"
	FROM bc
	WHERE ROWID < 25
	GROUP BY ^groupcol^
	]\")
);
dt_sql << Set Name("3");


// Summarize and transform (could also use exclude)
dt << Transform Column("A", Formula(If(Row() < 25, 1, .)));
Summarize(dt, groups = By(Eval(groupcol)), counts = Sum(:A)); // Summarize requires numeric columns
dt_summarize = New Table("4",
	New Column(groupcol, Character, Nominal, Values(groups)),
	New Column("N Rows", Numeric, Continuous, Values(counts))	
);
dt << Delete Columns(:A);

Write();