proc tabulate by two column factors / grouped within

[Cruise]

Hi guys,

I have two questions.

1. Image shows Wanted Output vs Current Output. What am I doing wrong in the code below?I'd like to show Medicaid 1 vs 0 given the age group.

2. How to switch the order of Medicaid 0 vs 1 to Medicaid 1 vs 0 in the output table?

Any suggestions appreciated.  

Thanks!!!

proc tabulate data=mydata order=internal; 
var id;
class medicaid A10 raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace
      type_of_reporting_source year sequence_number  tobacco marital_status_at_dx;
tables 
	(All raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace 
     tobacco marital_status_at_dx type_of_reporting_source year sequence_number), 
	id*(medicaid)*(N colpctn="%"*f=5.1) id*(medicaid*A10)*(colpctn="%"*f=5.1)/nocellmerge;
format...
run;

[ballardw · Accepted solution]

---

@Cruise wrote:

Hi guys,

I have two questions.

1. Image shows Wanted Output vs Current Output. What am I doing wrong in the code below?I'd like to show Medicaid 1 vs 0 given the age group.

2. How to switch the order of Medicaid 0 vs 1 to Medicaid 1 vs 0 in the output table?

Any suggestions appreciated.  

Thanks!!!

proc tabulate data=mydata order=internal; 
var id;
class medicaid A10 raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace
      type_of_reporting_source year sequence_number  tobacco marital_status_at_dx;
tables 
	(All raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace 
     tobacco marital_status_at_dx type_of_reporting_source year sequence_number), 
	id*(medicaid)*(N colpctn="%"*f=5.1) id*(medicaid*A10)*(colpctn="%"*f=5.1)/nocellmerge;
format...
run;

---

When you use the * between variables you are "nesting" the results. When the combination appears in a ROW expression the values are added left to right in the order of the expression. In a column expression they are added top to bottom in order and that is the way the grouping will display.

So your

id*(medicaid*A10)

has id on top, then Medicaid, then A10. Then to change the group order of the values

id*(A10* Medicaid)

I am afraid that to get exactly what you want is not going to be a simple one step change with your data or Proc Tabulate syntax. You would have to have 7 variables, one for each level of your current A10 and use something like

Medicaid(A10_1 - A10_7) assuming you created the variables with those names. And getting the correct values and statistic might be interesting. I suspect you might have to summarize all the data and then just use tabulate to display the results.

You can change the order of appearance of your Medicaid class variable by specifying the option DESCENDING on the CLASS statement. You would likely want to do that only for Medicaid in a separate CLASS statement. You may have multiple class statements to provide different class options for different variables. I would likely use a format so that the meaning of 0 and 1 appears in the table so I don't have to explain it to folks.

data example;
   do i= 1 to 20;
      x= mod(i,2);
      y= rand('uniform')*100;
      output;
   end;
run;

proc tabulate data=example;
   class x / descending;
   var y;
   table y*mean, 
          x
          /printmiss
   ;
run;

Your () on the row expression label of your table statement does nothing in this case. () are used to indicate a grouping of some sort to nest with one or more other variables and your row is not doing that

[Cruise]

This is excellent! This is close to perfect. I'm so happy and everything you said here make sense once you go through them. Totally doable but I couldn't figure. So below is the code reflected on your suggestions which solved the problem as in the new image. 

BRAOVO. Thanks @ballardw.

proc tabulate data=mydata order=internal; 
var id;
class A10 raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace
      type_of_reporting_source year sequence_number  tobacco marital_status_at_dx;
class medicaid/descending;
tables 
	(All raceth sex vital_status stage1 grade site surgery radiation area area1 birthplace 
     tobacco marital_status_at_dx type_of_reporting_source year sequence_number), 
	id*(medicaid)*(N colpctn="%"*f=5.1) id*(A10*medicaid)*(colpctn="%"*f=5.1)/nocellmerge printmiss;
format...
run;