How to transform matrix into 2D data table?

[#1]

Input:

Summarize(g=by(age, sex), c=count());
show(g, c);
output:
g:{{"12", "12", "13", "13", "14", "14", "15", "15", "16", "16", "17",
"17"}, {"F", "M", "F", "M", "F", "M", "F", "M", "F", "M", "F", "M"}}
c:[5,3,3,4,5,7,2,5,2,1,1,2]

How to make the result into a data table with column as sex and row as age? Like pivot table?

[#2]

You can use e.g. for-loops to populate a data table with those results.

However, a more direct way to obtain a pivot table is to use the summary command using subgroups.

Data Table( "Big Class" ) << Summary( Group( :age ), N, Subgroup( :sex ) );

[#3]

Please, are you perhaps looking for something as simple as a summary table of counts by age(rows) and sex(cols)? Example below. If more complicated please expand.


Best regards,
-Matt

[#4]

thanks. both are working. However, I need to have some other columns calulated based on the summary table and then put it into report. I think summary() might be more flexible.

[#5 · Accepted solution]

Tables->Summary is the way to create data tables that aggregate and summarize data. The Summary() message is the JSL way to create the data table.

Data Table( "Big Class" ) << Summary(
	Group( :age, :sex ),
	N,
	Freq( "None" ),
	Weight( "None" )
);

In contrast, the Summarize() function collects summary statistics for a data table and stores them in variables. This is useful if you need to use the summary statistics elsewhere in your script.

Summarize(g=by(age, sex), c=count());
show(g, c);
output:
g:{{"12", "12", "13", "13", "14", "14", "15", "15", "16", "16", "17",
"17"}, {"F", "M", "F", "M", "F", "M", "F", "M", "F", "M", "F", "M"}}
c:[5,3,3,4,5,7,2,5,2,1,1,2]