DATA Step, Macro, Functions and more

Proc Tabulate

Reply
Frequent Contributor
Posts: 140

Proc Tabulate

[ Edited ]

Hi,

 

I have this out put split by balance and volume. I am trying to turn this dataset into a proc tabulate but I can't work out where to add the volume, any ideas please?

 

Volume starts at 201609 - 2 - 2 - 2783 and I would like to append it to the balance within the tabulate, just as it is on the table below.

 

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

 

proc tabulate data = final_1 missing;

class fld1 /preloadfmt order=data mlf;

var flow_in flow_out flow_stk;

table (Sum="Balance" * fld1=""),

((flow_in="Flow In" * all="All")

(flow_out="Flow Out" * all="All")

(flow_stk="Default Stock" * all="All")) / box="Overdrafts";

run;

Super User
Posts: 6,964

Re: Proc Tabulate

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 797

Re: Proc Tabulate

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

Ask a Question
Discussion stats
  • 2 replies
  • 174 views
  • 0 likes
  • 3 in conversation