Exclude one or more variable´s options from output in proc tabulate

[deleted_user]

Hello, My doubt is the follow. I have one question (variable) that has three option.
I´d like to calculate the percentage of firms by region that mark option "1".
This I can do, but I dind´t get that the other two option are exclude of output.
The example below illustrate the case.

Instead of
REGIONS (RG)-----------------Q176----------------ALL
--------------------------------1----------2------------3
--------------------------------%---------%----------%-----N
SUDESTE---------------50.0-------50.0-------0.0----2
NORDESTE-----------100.0--------0.0-------0.0----1
CENTRO-OESTE-------0.0--------0.0----100.0----1
SUL-----------------------100.0-------0.0--------0.0----1
ALL--------------------------60.0-----20.0------20.0----5

I would like
REGIONS (RG)------Q176----------ALL
--------------------------------1----------
--------------------------------%------------N
SUDESTE---------------50.0-----------2
NORDESTE-----------100.0-----------1
CENTRO-OESTE-------0.0-----------1
SUL-----------------------100.0-----------1
ALL--------------------------60.0----------5

Sincerely

Edmundo

[deleted_user]

tabulate will do this if you have a little pre-processing which generates a variable =1 where the answer chosen was 1(else =0). Perhaps [pre]
data prepare/view=prepare ;
set answers ; * your original data set of answers ;
q176_1 = (q176=1) ;
q176_2 = (q176=2) ;
q176_3 = (q176=3) ;
run;
proc tabulate data= prepare ;
var q176_1 ;
class region ;
table ( region all), q176_1='Q176' * pctsum='1' * f=5.2
n='all'*f=7.
/ rts= 20 ;
run;

[deleted_user]

Thank you Peter, but I still have one problem with the percentage row column and total column.
These two columns was not calculate in a proper way. Look example below.

Right table

Region--------------------------------------(Q174)
-------------------------------------1---------------------2------------------------Total
------------------------n------%C------%L-----n-----%C------%L-------n------%C-----%L
Sudeste------------1------33,3----33.3-----2-----100,0----66.7----3------60,0---100.0
Centro-oeste-----1------33,3---100.0------.---------.---------.-------1------20,0---100.0
Sul-------------------1------33,3---100.0------.---------.---------. ------1------20,0---100.0
Total----------------3-----100,0-----60.0-----2----100,0----40.0-----5----100,0---100.0

I´d like to exclude the option 2, but remain the percentage calculus over all 5 observations.

Region------------------------(Q174)
-------------------------------------1-------------------Total
------------------------n--------%C------%L-------n-----% C----%L
Sudeste-------------1-------33,3-----33.3------3-----60,0---100.0
Centro-oeste-------1------33,3----100.0------1-----20,0---100.0
Sul--------------------1-------33,3----100.0------1-----20,0---100.0
Total-----------------3------100,0-----60.0------5----100,0---100.0


Sincerely

Edmundo

[deleted_user]

sorry, out of practise - I forgot the important feature to mention and demo :
A percentage can be derived as [pre]
sum( of analysis varA )
100 * ---------------------------
sum( of analysis varB )[/pre] with small change like: [pre]
data prepare/view=prepare ;
set answers ;
* your original data set of answers ;
q176_1 = (q176=1) ;
q176_2 = (q176=2) ;
q176_3 = (q176=3) ;
retain one 1 ;
run;
proc tabulate data= prepare ;
var q176_1 one;
class region ;
table ( region all), q176_1='Q176'
* pctsum='1' * f=5.2
n='all'*f=7.
/ rts= 20 ;
run;
[/pre] try that approach....
At the back of my mind I feel the introduction of the "one" variable should be unneccessary, perhaps using pctn.
However, the important addition here is that using an analysis variable as percentage denominator definition, gives you a ratio of two analysis variables instead of the summary of each value of the class variable.

Good Luck

Peter-c

[deleted_user]

Many thanks Peter, you did it. Your approach work well.

You have a deep knowledge about SAS indeed.

sincerely

Edmundo