I generally agree with the conventional wisdom on the advantages of structuring data in a long vs wide format.
But in this case - 2 rows (a sum row followed by a frequency row), a set of ID variables (only NAME in this case), and any number of columns, there is really no need for transpose, assuming you really do want an additional row of averages:
data sample;
input name$ june july;
datalines ;
amount 170 140
account 2 4
run;
data want;
set sample end=end_of_sample;
output;
array vals _numeric_;
do over vals;
vals=lag(vals)/vals;
end;
if end_of_sample then do;
name='Average';
output;
end;
run;
This will produce the following log note:
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
2 at 19:19
which can be avoided by using
vals=sum(0,lag(vals))/vals;
inside the "DO OVER VALS" loop.
... View more
