topic how to divide two proc tabulate tables through each other without using Excel? in SAS Programming

how to divide two proc tabulate tables through each other without using Excel?

EC27556 — Mon, 09 May 2022 17:04:04 GMT

Hi, I currently have 2 tables that I produce via proc tabulates using the below code:

title 'TableA';
proc tabulate data = Example out = Numerator;
class GroupA GroupB GroupC GroupD ap_month;
var count one;
table GroupA=''*GroupB=''*GroupC=''*GroupD=''*sum=''*(count*f=comma14.0),month  /nocellmerge;
run;

title 'TableB';
proc tabulate data = Example out = Denominator;
class GroupA GroupB GroupC GroupD month;
var count one;
table GroupA=''*GroupB=''*GroupC=''*GroupD=''*sum=''*(one*f=comma14.0),month  /nocellmerge;
run;

The tables look like this:

by dividing through one another you get probabilities and create a probability matrix.

So what the table is basically saying is, if groupA=1 and GroupB,C,D=0 then probabilities in each month are the top line of the first table divided by the top line of bottom table.

I would like to create some code that does this dividing to create the same table with probabilities without having to manually divide one table through another in excel.

Does anyone know if this is possible?

NB. the "count variable" represents whether someone has had a certain event (this is what the probabilities relate to - the probabilities of having this event) and the variable "one" is a constant variable that =1 for all observations in the data. If people are in different groups then the probability of having the event is different.

Thanks

Re: how to divide two proc tabulate tables through each other without using Excel?

Reeza — Mon, 09 May 2022 17:18:16 GMT

Yes, it I would recommend using PROC FREQ to get the counts needed and piping those results to data sets using an OUT= data set.
Then you can merge the two data sets together and do the calculations necessary. How exactly you do the merge depends on your criteria. Then use PROC REPORT to display the data as needed.

Re: how to divide two proc tabulate tables through each other without using Excel?

EC27556 — Mon, 09 May 2022 17:52:40 GMT

Thanks for this. Are you able to provide any example code? I have little experience with Proc Freq and Report!

Re: how to divide two proc tabulate tables through each other without using Excel?

EC27556 — Mon, 09 May 2022 18:28:09 GMT

Never mind, I've worked it out! Using the output from the above proc tabulates you need to run the following code:

data Merged;
merge TableA(drop = _TYPE_ _PAGE_ _TABLE_)
	  TableB(drop = _TYPE_ _PAGE_ _TABLE_);
by GroupA GroupB GroupC GroupD Month;

Probability=Count_Sum/One_Sum;
run;



proc report data=Merged;
	
	column GroupA GroupB GroupC GroupD month,(Probability);
	
	define Month / Across;
	define GroupA / Group;
	define GroupB / Group;
	define GroupC / Group;
	define GroupD / Group;
	define Probability / sum;
run;