Combine 3 Proc-Tabulate-Steps

[Konkordanz]

Hi,

I want to build a table with proc-tabulate and want to construct it with one single step. But I dont know, if this is actually doable. For example: The columns shall represent the the number of men. Thr first line shall be the number in general, the second shall be the number of men with a height>60 and the third one shall give me the number of men with an height >60 and a name, that starts with an "J".

As you can see: I have multiple filters. My hope: Are multiple where-conditions within one proc-tabulate or within the table-statement possible? I tried different things but it didnt work.

So: Do you have a solution, to combine these 3 have-datasets? Thank you!

proc tabulate data=sashelp.class;
class sex age;
table sex, age;
where sex="M";
run;

proc tabulate data=sashelp.class ;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60;
run;

proc tabulate data=sashelp.class ;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60 and name like "J%";
run;

[GinaRepole]

Unfotunately, it isn't possible to use multiple WHERE filters in the same step as you're hoping to accomplish, regardless of what PROC you've selected. The nature of the WHERE statement is that it filters the data prior to processing your request, which means the data will need to be read an additional time to include a different subset of rows. That additional reading is triggered by running a new step.

That being said, it doesn't mean it's impossible to get the type of output you're looking for. It will probably just be a bit less sleek or require different techniques than you hoped. Here's a quick, basic version I wrote up that I think will give you roughly what you wanted for this sample data, but it's just what came to mind first (aka not optimized) and I'm sure many folks would happily take my version and improve upon it. Happy to see the results if they do and it helps you find a version you like better for your needs! 🙂

/* These first three proc tabulates are the same as your initial code with */
/* one minor tweak; these output the data to separate datasets so they can */
/* be manipulated and modified more easily.                                */
proc tabulate data=sashelp.class out=work.first;
class sex age;
table sex, age;
where sex="M";
run;

proc tabulate data=sashelp.class out=work.second;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60;
run;

proc tabulate data=sashelp.class out=work.third;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60 and name like "J%";
run;

/* The following data step combines all previous results, and adds a */
/* "tab" column to identify which original proc tabulate the row is  */
/* from.															 */
data work.combo;
	set work.first(in=a)
		work.second(in=b)
		work.third(in=c);
	if a then tab="a";
	else if b then tab="b";
	else if c then tab="c";
run;

/* When proc tabulate outputs data, it flips it around to save more */
/* "vertically" instead of the way it prints "horizontally." The    */
/* following proc transpose will flip things back around so that    */
/* the style will more closely match the original proc transpose.   */
proc transpose data=work.combo out=work.trans(drop=tab _name_);
	var N;
	by tab;
	id age;
run;

/* Modifications are done, so we'll print to show it off! */
proc print data=work.trans;
run;

[Konkordanz]

Thank you for your answers. I guess, @GinaRepole´s solution is the most helpful option for me. With the simple example its pretty easy to reproduce the result. But with my dataset it isnt. The problem is the proc-transpose-step. I tried several ways but i got error messages. 

Would be fine, if you could help me again.

Here is a snipped of my current dataset (as the result of the work.combo-data-step):

have:

What I want is to transpose it like this:

The work.combo-dataset seems to be sorted. So I tried it with following steps:

proc sort data=kombi;
by LFBP_erwtyp3 LF_Typ30 tab HRF_P_Sn_N HRF_P_Sn_Sum;
run;

proc transpose data=work.kombi out=work.trans prefix=LF_Typ30;
	var HRF_P_SN_Sum HRF_P_Sn_N;
	by LFBP_erwtyp3;
	id  LF_Typ30;
	run;

But the result is for example: "ERROR: The ID value "LF_Typ3001_A_ERZ_1Ki" occurs twice in derselben BY-Gruppe." and "ERROR: All BY groups were bad."

Where is the problem of my code?

[ballardw]

One way is to create 1/0 (or 1 when true) coded variables and SUM them instead of counting from your existing data in a new data set and use that.

One variable for each condition.

data fortab;
   set sashelp.class;
   ism = (sex='M');
   istall = (height>60);
   isname = (name =:'J');
   istallmale = ism and istall;
   istallmalej= istallmale and isname;
run;

proc tabulate data=fortab;
   class age;
   var ism istallmale istallmalej;
   table (ism istallmale istallmalej)* sum='',
         age
   ;
   label ism='Male'
         istallmale='Male height>60'
         istallmalej ='Male Height>60 name starts with J'
   ;
run;

I have more variables than needed. Exercise for the interested read add just the tall and the names starting with J.

---

@Konkordanz wrote:

Hi,

I want to build a table with proc-tabulate and want to construct it with one single step. But I dont know, if this is actually doable. For example: The columns shall represent the the number of men. Thr first line shall be the number in general, the second shall be the number of men with a height>60 and the third one shall give me the number of men with an height >60 and a name, that starts with an "J".

As you can see: I have multiple filters. My hope: Are multiple where-conditions within one proc-tabulate or within the table-statement possible? I tried different things but it didnt work.

So: Do you have a solution, to combine these 3 have-datasets? Thank you!

proc tabulate data=sashelp.class;
class sex age;
table sex, age;
where sex="M";
run;

proc tabulate data=sashelp.class ;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60;
run;

proc tabulate data=sashelp.class ;
class sex age;
var height;
table sex, age;
where sex = "M" and height>60 and name like "J%";
run;

---

[Reeza]

Maybe, with some manipulation?

data class;
set sashelp.class;
if sex='M' then group=1;
if group=1 and height>60 then group=2;
if group=2 and substr(name, 1, 1) = 'J' then group=3;
run;

proc format;
value fmt_group (multilabel)
1,2,3 = 'Male'
2,3 = 'Male over 60cm'
3 = 'Male, over 60 cm, name starts with J';
run;


proc tabulate data=class;
class group / mlf;
format group fmt_group.;
class sex age;
table group*sex, age / misstext='0';
run;

Results

		Age
		11	12	13	14	15	16
		N	N	N	N	N	N
group	Sex	1	3	1	2	2	1
Male	M
Male over 60cm	M	0	1	1	2	2	1
Male, over 60 cm, name starts with J	M	0	0	1	0	0	0