topic How can I merge multiple tables from PROC TABULATE? in SAS Programming

How can I merge multiple tables from PROC TABULATE?

arra — Fri, 15 Dec 2023 10:07:53 GMT

Dear SAS community,

I am asked to create a table with this formatting:

How should I modify my code to merge the four tables?

proc tabulate data=sashelp.class out=temp;
  	var age height weight;
  	class sex;
  	table height='height: mean (std)', (sex all)*(mean='' std='');
	table height='height: min-max', (sex all)*(min='' max='');
	table weight='weight: mean (std)', (sex all)*(mean='' std='');
	table weight='weight: min-max', (sex all)*(min='' max='');
run;

For simplicity, the code for the brackets around std and the dash between min and max is omitted. The actual application involves multiple tables with many variables.

Re: How can I merge multiple tables from PROC TABULATE?

mkeintz — Fri, 15 Dec 2023 13:33:34 GMT

Your desired table has columns with different statistics across rows (mean then min in one column, std then max in another column). PROC TABULATE can't do this.

I think it's doable in PROC REPORT, for which others can likely provide guidance.

Re: How can I merge multiple tables from PROC TABULATE?

Patrick — Fri, 15 Dec 2023 14:13:31 GMT

Hi @arra

Welcome to the SAS Communities.

Whenever you ask coding questions then it's really appreciated if you also provide some representative sample data together with the desired result. It's also really helpful if you provide the code you've tried already whether it's fully working or not - as you did 🙂

Representative sample data is what allows the ones responding to actually test the proposed code. Such data also often removes a lot of ambiguity in the description of the problem.

Please provide the sample data via a tested SAS data step with a datalines/card statement that creates a SAS table. This allows people to spend the time answering your question instead of preparing sample data.

Asking a question this way takes a bit more time but I promise you that you will get all the time back by getting a solution faster.

Thanks,

Patrick

Re: How can I merge multiple tables from PROC TABULATE?

arra — Fri, 15 Dec 2023 14:20:31 GMT

This is why I used the built-in sample data (data=sashelp.class). Or do you mean something else?

Re: How can I merge multiple tables from PROC TABULATE?

Astounding — Fri, 15 Dec 2023 14:33:18 GMT

Do you have a picture in mind for the final result?

Here is a possible (untested) approach for you to consider:

proc tabulate data=sashelp.class out=temp;
  	var age height weight;
  	class sex;
  	table height='height: mean (std)'*(mean=' ' std=' ')
	      height='height: min-max'*(min=' ' max=' ')
	      weight='weight: mean (std)'*(mean=' ' std=' ')
 	      weight='weight: min-max'*(min=' ' max=' ')
          ,
           (sex all);
run;

Re: How can I merge multiple tables from PROC TABULATE?

Patrick — Fri, 15 Dec 2023 15:14:31 GMT

@arra wrote:
This is why I used the built-in sample data (data=sashelp.class). Or do you mean something else?

Oh, missed that! You're too perfect!

Re: How can I merge multiple tables from PROC TABULATE?

arra — Fri, 15 Dec 2023 14:39:25 GMT

Thanks. Sure, my post includes an image of the required result. (The proposed solution does not put different summary statistics in the same row.)

Re: How can I merge multiple tables from PROC TABULATE?

Patrick — Fri, 15 Dec 2023 15:25:25 GMT

I believe using Proc Tabulate below are your two options - which aren't exactly what you had in mind.

title 'Option 1';
proc tabulate data=sashelp.class out=temp;
  var age height weight;
  class sex;
  table (height='height: mean (std)' weight='weight: mean (std)'), 
        (sex all)*(mean=' ' std=' ');
  table (height='height: min-max' weight='weight: min-max'), 
        (sex all)*(mean=' ' std=' ');
run;

title 'Option 2';
proc tabulate data=sashelp.class out=temp;
  var age height weight;
  class sex;
  table (height weight)*(mean std min max), 
        (sex all);
run;
title;

I'm not sure if Proc Report would allow for the layout you have in mind.

....and if you really need full flexibility then there is the Report Writing Interface - but that's a whole different ball game and the code @Ksharp shared in this thread should give you the idea what it entails.

Re: How can I merge multiple tables from PROC TABULATE?

arra — Fri, 15 Dec 2023 15:39:43 GMT

Thanks. Option 2 is what I currently have. I will try to get it through.

Re: How can I merge multiple tables from PROC TABULATE?

A_Kh — Fri, 15 Dec 2023 17:13:05 GMT

For creating custom format tables, which includes inconsistent values per column, data is usually manipulated in data step.

In this specific case I would do the following steps (there might be more efficient ways to do it):

*step 1; 
proc means data=sashelp.class nway;
	ways 1; 
	class sex;
	var height weight;
	output out=means1; 
run; 
proc means data=sashelp.class;
	var height weight;
	output out=means2; 
run; 

*step 2; 
proc transpose data=means1 out=wide1;
	by sex;
	var height weight;
	id _stat_;
proc print;run; 
proc transpose data=means2 out=wide2;
	by _type_;
	var height weight;
	id _stat_;
proc print;run; 

*step 3; 
data wide1a;
	set wide1;
	length M_STD MIN_MAX $15; 
	M_STD= strip(put(mean, 6.2))||' ('||strip(put(std, 6.2))||')';
	MIN_MAX= strip(put(min, best.))||' - '||strip(put(max, best.));
proc print; run; 
proc sort data= wide1a; by _name_; run; 
proc transpose data=wide1a out=narrow1;
	by _name_ ;
	var m_std min_max;
	id sex; 
proc print; run;  

data wide2a;
	set wide2; 
	length M_STD MIN_MAX $15; 
	M_STD= strip(put(mean, 6.2))||' ('||strip(put(std, 6.2))||')';
	MIN_MAX= strip(put(min, best.))||' - '||strip(put(max, best.));
proc print; run; 
proc transpose data=wide2a out=narrow2 (rename=(col1=TOTAL));
	by _name_ ;
	var m_std min_max;
proc print; run;  

*step 4; 
data final;
	merge narrow1 narrow2;
	by _name_; 
	if find(f, '(') gt 0 then stat= 'mean (std)';
	else do;
		stat= 'min - max';
		_name_='';
	end; 
proc print; run; 

proc report data= final split='~';
	column _name_ stat('Sex' f m) ('Both~' total);
	define _name_/'' display;
	define stat/'' display;
	define f/ 'F' display; 
	define m/'M' display; 
	define total/'' display; 
run;

result;

Re: How can I merge multiple tables from PROC TABULATE?

arra — Mon, 18 Dec 2023 08:19:12 GMT

Many thanks for this solution! This is much more efficient than manual formatting! (I now see of course that my question should not have mentioned PROC TABULATE.)