topic Re: 2 dimensional table with control of the order of categories in SAS Programming

2 dimensional table with control of the order of categories

Ronein — Tue, 07 Jan 2020 08:13:00 GMT

Hello

I am using proc tabulate for 2 dimensional table.

The problem is that the order of the categorical values is not as I wish.

The required orders of the categories are:

order of "fileld" : max ,S_Max
order of "factor " : 1.2,1.1,1.0
order of "Amnt" : Null,50000,20000
PCT order: Null,50,40,30

What is the way to do it please?

I also want to add extra row of totals

I also want to add extra column of totals

Data tbl;
INFILE DATALINES DLM=',';
input field $ PCT Amnt   Factor	Reduce ;
cards;
max,.,.,1,-4490
max,30,20000,1,-2461
max,40,20000,1,-2981
max,50,20000,1,-3433
max,30,50000,1,-3621
max,40,50000,1,-3843
max,50,50000,1,-4039
max,.,.,1.1,-4157
max,30,20000,1.1,-2306
max,40,20000,1.1,-2788
max,50,20000,1.1,-3202
max,30,50000,1.1,-3361
max,40,50000,1.1,-3568
max,50,50000,1.1,-3749
max,.,.,1.2,-3858
max,30,20000,1.2,-2161
max,40,20000,1.2,-2607
max,50,20000,1.2,-2988
max,30,50000,1.2,-3125
max,40,50000,1.2,-3319
max,50,50000,1.2,-3486
S_Max,.,.,1,-5114
S_Max,30,20000,1,-2697
S_Max,40,20000,1,-3281
S_Max,50,20000,1,-3802
S_Max,30,50000,1,-4094
S_Max,40,50000,1,-4342
S_Max,50,50000,1,-4564
S_Max,.,.,1.1,-4804
S_Max,30,20000,1.1,-2573
S_Max,40,20000,1.1,-3122
S_Max,50,20000,1.1,-3606
S_Max,30,50000,1.1,-3859
S_Max,40,50000,1.1,-4093
S_Max,50,50000,1.1,-4301
S_Max,.,.,1.2,-4514
S_Max,30,20000,1.2,-2449
S_Max,40,20000,1.2,-2964
S_Max,50,20000,1.2,-3415
S_Max,30,50000,1.2,-3637
S_Max,40,50000,1.2,-3857
S_Max,50,50000,1.2,-4052
;
run;


proc tabulate data=tbl;
	class Amnt PCT field Factor / missing;
	var Reduce;
	tables (Amnt*PCT),(field*Factor)*(Reduce*mean) ;
run;
field order : max ,S_Max
factor order: 1.2,1.1,1.0
Amnt order:   Null,50000,20000
PCT order:    Null,50,40,30

Re: 2 dimensional table with control of the order of categories

Oligolas — Wed, 08 Jan 2020 09:27:02 GMT

Use formats to create new variables with ordinal values. Run PROC TABULATE and decode the ordinal values back to original.

proc format;
invalue field_code
   'max'   = 1
   'S_Max' = 2
   ;
invalue factor_code
   '1.2' = 1
   '1.1' = 2
   '1'   = 3
   ;
invalue Amnt_code
   ' '     = 1
   '50000' = 2
   '20000' = 3
   ;
invalue PCT_code
   ' '  = 1
   '50' = 2 
   '40' = 3
   '30' = 4
   ;

value field_decode
   1 = 'max'
   2 = 'S_Max'
   ;
value factor_decode
   1 = 1.2
   2 = 1.1
   3 = 1
   ;
value Amnt_decode
   1 = .
   2 = 50000
   3 = 20000
   ;
value PCT_decode
   1 = .
   2 = 50
   3 = 40
   4 = 30
   ;
run;

data tbl2;
   set tbl;
   field_code=input(field,field_code.);
   factor_code=input(strip(put(factor,best.)),factor_code.);
   Amnt_code=input(strip(put(Amnt,best.)),Amnt_code.);
   PCT_code=input(strip(put(PCT,best.)),PCT_code.);
run;
proc tabulate data=tbl2 ;
	class Amnt_code PCT_code field_code factor_code / missing;
	var Reduce;
	tables (Amnt_code*(PCT_code='' all='Total')),(field_code*(factor_code='' all='Total'))*(Reduce*mean);
   format field_code field_decode. factor_code factor_decode. Amnt_code Amnt_decode. PCT_code PCT_decode.;
run;

Edit:add totals

Re: 2 dimensional table with control of the order of categories

1zmm — Tue, 07 Jan 2020 16:27:42 GMT