topic Proc Tabulate: percentage-problem in SAS Programming

Proc Tabulate: percentage-problem

Konkordanz — Wed, 28 Jun 2023 04:48:07 GMT

Hi,

is there any description of the logic of proc tabulate? I doesnt get it, how the logic is with all the brackets...how do I have to set them, where shall I set a Multiplication sign etc.?

For hours I try to get a table like this:

	kreis A			kreis B			kreis C
	arbeit			arbeit			arbeit
wirtschaftsbereich	<32h	>32h	total	<32h	>32h	total	<32h	>32h	total
A	40%	60%	100%	30%	70%	100%	50%	50%	100%
B	…	…	…	…	…	…	…	…	…
C	…	…	…	…	…	…	…	…	…

But what I get is something like that:

	kreis A			kreis B			kreis C
	arbeit			arbeit			arbeit
wirtschaftsbereich	<32h	>32h	rowpctn	<32h	>32h	rowpctn	<32h	>32h	rowpctn
A	0,06	0,26	1,64%	0,48	1,34	7,21%	0,34	2,35	9,84%
B	…	…	…	…	…	…	…	…	…
C	…	…	…	…	…	…	…	…	…

I want all the data as percentage (per group; <32h, >32h, total).

I just can't get the Total column to be 100% and each of the two columns ("work") before it. Thats my syntax - pls help me. Thank you!


proc tabulate data=xy;
class Wirtschaftsbereich Kreis_3st Arbeit;
classlev Wirtschaftsbereich / style=[cellwidth=11cm];
var HRF;
table (Wirtschaftsbereich All),	HRF*Kreis_3st*(Arbeit RowPctN);
format 	Wirtschaftsbereich Sektor.
		Arbeit Arbeit.
		Kreis_3st $Kreis_3st.;
run;

Re: Proc Tabulate: percentage-problem

JosvanderVelden — Wed, 28 Jun 2023 09:38:11 GMT

Have a look at this paper: https://support.sas.com/resources/papers/proceedings/proceedings/sugi30/258-30.pdf

Re: Proc Tabulate: percentage-problem

ballardw — Wed, 28 Jun 2023 14:30:14 GMT

Brackets, or probably more correctly parentheses, are used when a group of items are to be used together. Without an explicit description of what you attempt, i.e. given actual input data set and description of denominators and numerator given your subject of "percentage-problem" it is hard to tell where they may be appropriate.

In proc tabulate the * is not a "multiplication" it indicates a nesting level var1*var2*var3 for class variables means levels of Var3 within levels of Var2 within levels of Var1.

Since Proc Tabulate calculates Percent of N and Percent of Sum for table, page, column and/or row and may involve the N or Sum of a specific variable it is very important to clearly define exactly which percentage you are discussing.

When you get results that do not match your desire then provide 1) the code 2) example data in the form of working data step code 3) the definitions of numerator and denominator needed.

I would guess from your output that you likely used PCTN where you want ROWPCTN coupled with All in the correct dimension for the total. Since you don't provide even the names of variables I am not going to guess where you want anything.

Here is small example of tabulate calculating multiple percent of n's with the actual n included so you could check which you want. The SASHELP.CLASS set is small enough you should be able to check some of this by eye or hand calculation easily enough.

proc tabulate data=sashelp.class;
   class age sex;
   table age,
         (sex all)*(n pctn colpctn rowpctn )
   ;
run;

Which yields:

	Sex								All
	F				M				All
	N	PctN	ColPctN	RowPctN	N	PctN	ColPctN	RowPctN	N	PctN	ColPctN	RowPctN
Age	1	5.26	11.11	50.00	1	5.26	10.00	50.00	2	10.53	10.53	100.00
11	1	5.26	11.11	50.00	1	5.26	10.00	50.00	2	10.53	10.53	100.00
12	2	10.53	22.22	40.00	3	15.79	30.00	60.00	5	26.32	26.32	100.00
13	2	10.53	22.22	66.67	1	5.26	10.00	33.33	3	15.79	15.79	100.00
14	2	10.53	22.22	50.00	2	10.53	20.00	50.00	4	21.05	21.05	100.00
15	2	10.53	22.22	50.00	2	10.53	20.00	50.00	4	21.05	21.05	100.00
16	.	.	.	.	1	5.26	10.00	100.00	1	5.26	5.26	100.00