topic PROC TABULATE-N SUM PCTN PCTSUM-order of rows in SAS Programming

PROC TABULATE-N SUM PCTN PCTSUM-order of rows

Ronein — Thu, 19 Sep 2019 05:08:09 GMT

Hello

I have raw data that contain 3 columns: customer ID, score, obligation.

I need to create the following summary table:

Class variable: Score (categories will be defined by proc format)

Need to calculated:

Number of customer of each category

Sum of obligation for each category

%customers from Total customers for each category (without categories 13,14)

%Total obligation from Total obligation for each category (without categories 13,14)

The problem is that in the output table the order of the rows (categories) is not as I wish.

The order should be :

'0'
'2--7'
'8'
'9--10'
'11'
'12'

TOTAL 0-12
'13'
'14'

But in this code the Total 0-12 appears in first row and also I need to change the name of this category from null (.) to TOTAL 0-12

data rawtbl;
input ID score obligo;
cards;
1 0 10
2 0 12
3 4 14
4 3 16
5 3 18
6 8 20
7 8 22
8 8 24
9 11 26
10 9 28
11 12 30
12 11 32
13 13 34
14 14 36
15 14 38
;
run;


proc format ;
value ffmt
0='0'
2-7='2--7'
8='8'
9-10='9--10'
11='11'
12='12'
13='13'
14='14'
;
Run;


ods select none;
proc tabulate data=rawtbl f=comma18. out=summary1 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables score ALL, ID*N   obligo*sum  ;
format score ffmt.;
run;
ods select all;



ods select none;
proc tabulate data=rawtbl(where=(score between 0 and 12)) f=comma18. out=summary2 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables score ALL, ID*PCTN   obligo*PCTSUM  ;
format score ffmt.;
run;
ods select all;


proc sort data=summary1;by score;run;
proc sort data=summary2;by score;run;

Data Summary3;
Merge summary1(in=a) summary2(in=b);
by score;
Run;

Re: PROC TABULATE-N SUM PCTN PCTSUM-order of rows

Shmuel — Thu, 19 Sep 2019 08:55:37 GMT

I run your code and the total was at the bottom of the table labeled as 'ALL'.

You can change the label as in:

proc tabulate data=rawtbl f=comma18. out=summary1 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables score ALL='Total', ID*N   obligo*sum  ;
format score ffmt.;
run;

If you prefer to see the total on firs row then :

proc tabulate data=rawtbl f=comma18. out=summary1 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables ALL='Total' score , ID*N   obligo*sum  ;
format score ffmt.;
run;

Re: PROC TABULATE-N SUM PCTN PCTSUM-order of rows

ballardw — Thu, 19 Sep 2019 14:50:39 GMT

@Ronein wrote:

Hello

I have raw data that contain 3 columns: customer ID, score, obligation.

I need to create the following summary table:

Class variable: Score (categories will be defined by proc format)

Need to calculated:

Number of customer of each category

Sum of obligation for each category

%customers from Total customers for each category (without categories 13,14)

%Total obligation from Total obligation for each category (without categories 13,14)

The problem is that in the output table the order of the rows (categories) is not as I wish.

The order should be :

'0'
'2--7'
'8'
'9--10'
'11'
'12'

TOTAL 0-12
'13'
'14'

But in this code the Total 0-12 appears in first row and also I need to change the name of this category from null (.) to TOTAL 0-12
data rawtbl;
input ID score obligo;
cards;
1 0 10
2 0 12
3 4 14
4 3 16
5 3 18
6 8 20
7 8 22
8 8 24
9 11 26
10 9 28
11 12 30
12 11 32
13 13 34
14 14 36
15 14 38
;
run;


proc format ;
value ffmt
0='0'
2-7='2--7'
8='8'
9-10='9--10'
11='11'
12='12'
13='13'
14='14'
;
Run;


ods select none;
proc tabulate data=rawtbl f=comma18. out=summary1 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables score ALL, ID*N   obligo*sum  ;
format score ffmt.;
run;
ods select all;



ods select none;
proc tabulate data=rawtbl(where=(score between 0 and 12)) f=comma18. out=summary2 (drop=_type_ _page_ _table_) ;
class score ;
var obligo  ID;
tables score ALL, ID*PCTN   obligo*PCTSUM  ;
format score ffmt.;
run;
ods select all;


proc sort data=summary1;by score;run;
proc sort data=summary2;by score;run;

Data Summary3;
Merge summary1(in=a) summary2(in=b);
by score;
Run;

Since you do not show what you actually expect [HINT] I am going to guess that maybe what you want is something like:

proc format library=work;
value ffmt (multilabel notsorted)
0='0'
2-7='2--7'
8='8'
9-10='9--10'
11='11'
12='12'
0 - 12 = 'Total 0-12'
13='13'
14='14'
;
Run;

proc sort data=work.rawtbl;
  by score;
run;

proc tabulate data=work.rawtbl f=comma18. out=summary1 (drop=_type_ _page_ _table_) ;
class score /mlf order=data preloadfmt;
var obligo  ID;
tables score , ID*N   obligo*sum  ;
format score ffmt.;
run;

Re: PROC TABULATE-N SUM PCTN PCTSUM-order of rows

Ronein — Thu, 26 Sep 2019 18:41:59 GMT

Sorry fort late response and thank you.

I want to calculate following 4 statistics:

a- SUM of obligation for following categories: 0 ,2-7,8,9-10,11,12,0-12,13,14

b-Number of customers for following categories: 0 ,2-7,8,9-10,11,12,0-12,13,14

c-Percent of SUM of obligation from total for following categories: 0 ,2-7,8,9-10,11,12,0-12 (without 13,14 ).

As you understand for calculation c the total obligation will be calculated from categories 0-12

d-Percent of number of customers from total for following categories: 0 ,2-7,8,9-10,11,12,0-12 (without 13,14 ).

As you understand for calculation d the total of customers will be calculated from categories 0-12

Note:

In the output I want to see the categories in the following logic order

0 ,2-7,8,9-10,11,12,0-12,13,14

I want to dispaly also categories with summary statistics value 0