topic Re: Proc Tabulate in SAS Programming

Proc Tabulate

anonymous_user — Fri, 13 Oct 2017 09:12:49 GMT

Re: Proc Tabulate

Kurt_Bremser — Mon, 21 Nov 2016 12:16:00 GMT

Wouldn't a simple proc report achieve it?

proc report data=final_1;
column type fld1 flow_in flow_out flow_stk;
define type / group;
define fld1 / display;
define flow_in / display;
define flow_out / display;
define flow_stk / display;
run;

You will have to do some "prettifying", of course.

Edit: ups, forgot to show how I created "type":

data final_1;
input type $ fld1 $ flow_in flow_out flow_stk;
cards;
Balance  201610 	0.19 	0.96 	0.76
Balance  201609 	0.15 	1.00 	0.02
Balance  201608 	0.23 	0.96 	0.17
Balance  201607 	0.93 	0.88 	0.85
Balance  201606 	0.70 	0.15 	0.06
Balance  201605 	0.41 	0.83 	0.06
Balance  201604 	0.71 	0.56 	0.21
Balance  201603 	0.66 	0.60 	0.51
Balance  201602 	0.48 	0.39 	0.22
Balance  201601 	0.91 	0.28 	0.35
Balance  201512 	0.86 	0.33 	0.26
Balance  201511 	0.87 	0.29 	0.65
Balance  201510 	0.50 	0.28 	0.90
Balance  201509 	0.08 	0.79 	0.63
Volume  201610 	379 	396 	2766
Volume  201609 	2 	2 	2783
Volume  201608 	1 	411 	1
Volume  201607 	6 	8 	0
Volume  201606 	2 	89 	121
Volume  201605 	3 	185 	2
Volume  201604 	2 	9 	2881
Volume  201603 	47 	98 	0
Volume  201602 	58 	8 	0
Volume  201601 	455 	989 	5
Volume  201512 	47 	8 	658
Volume  201511 	1 	202 	2548
Volume  201510 	47 	272 	2398
Volume  201509 	. 	. 	2372
;
run;

PS please supply your example data in a data step (as I did), so we can get the exact same types and formats that you have.

Re: Proc Tabulate

mkeintz — Mon, 21 Nov 2016 12:47:09 GMT

You say that volume starts at the record with 201609 2 2 2783. Are you saying that the first 15 records are balance and the remaining 13 records are volume? Why not 14 and 14, with volume starting at 201610 379 396 2766?

It looks like you need to make a new class variable MEASURE_TYPE='Balance' for the first half of your data set and MEASURE_TYPE='Volume' for the second half:

data final_1a;
  set final_1  nobs=nrecs;
  if _n_<=nrecs/2 then measure_type='Balance';
  else measure_type='Volume';
run;

Then you could put the balance and volume stats side-by-side, as in

proc tabulate data = final_1a missing;
  class fld1 measure_type /preloadfmt order=data mlf;
  var flow_in flow_out flow_stk;

 table (Sum=" " * fld1=""),
  measure_type=' '*
   ((flow_in="Flow In" * all="All")
   (flow_out="Flow Out" * all="All")
   (flow_stk="Default Stock" * all="All")) 
  / box="Overdrafts" 
    row=float rtspace=12;
run;

regards,

Mark