Hi,
I have a file structured like this :
data A;
dept = "Dept1"; year=2016; budget=10; spending=5; transfer=5; output;
dept= "Dept1"; year=2015; budget=8; spending=4; transfer=4; output;
dept= "Dept1"; year=2014; budget=8; spending=6; transfer=2; output;
dept = "Dept2"; year=2016; budget=100; spending=50; transfer=50; output;
dept= "Dept2"; year=2015; budget=80; spending=40; transfer=40; output;
dept= "Dept2"; year=2014; budget=80; spending=60; transfer=20; output;
run;
and I want to get a file with the following structure (wihtout knowing in advance how many years are in file A):
DEPT Budget2016 Spending2016 Transfer2016 Budget2015 Spending2015 Transfer2015
Budget2014 Spending2014 Transfer2014
Currently I m doing 3 proc transpose with a prefix to get files B, C and D :
proc transpose data = A out = B (drop=_:) prefix=budget;
id year; var budget ; by DEPT ; run;
etc.
Then I merge B,C and D into file E but the problem is E has a structure that is useless to the users :
DEPT Budget2016 Budget2015 Budget2014 Spending2016 Spending2015 Spending2014 Transfer2016 Transfer2015 Transfer2014
To fix this issue I do retain statement :
DATA F ;
retain DEPT Budget2016 Spending2016 Transfer2016 Budget2015 Spending2015 Transfer2015
Budget2014 Spending2014 Transfer2014 ;
set E;
run;
But this program is not satisfactory because I don't know in advance how many years I am going to get in File A : it can be from 2 years to 10 years, depending on the user's request.
On internet, I found a solution using proc summary (see below) for a similar problem but I don't know how to write the line idgroup(out[7] (la ln)=) / autoname; to solve my problem.
proc summary data=have nway;
class acct seq1 seq2;
output out=want (drop=_:)
idgroup(out[7] (la ln)=) / autoname;
run;
Thanks.
... View more
