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.
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.