07-26-2016 01:12 PM
I have a file structured like this :
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;
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;
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 ;
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 (la ln)=) / autoname; to solve my problem.
proc summary data=have nway;
class acct seq1 seq2;
output out=want (drop=_
idgroup(out (la ln)=) / autoname;
07-26-2016 02:32 PM
This is a good use for macro language. For example, you could begin by getting a list of all the YEAR values in the data:
select distinct year into : year_list separated by ' ' from A;
That would give you a macro variable just as if you had coded:
%let year_list = 2014 2015 2016;
If you are comfortable proceeding in this way, I can spell out the rest. It does get a bit more complex, but if you are comfortable with a little macro language it's not horrible.
07-26-2016 02:37 PM
I use macro language;
As I mentioned, I was oriented toward the proc summary, but if the solution comes from a macro, that's fine.
07-26-2016 03:08 PM
OK, let's assume you already ran the SQL step and have a macro variable YEAR_LIST. The list will be from lowest to highest year. So you will need to go through the list from right to left, to go from highest to lowest year.
%local i next_year;
%do i=%sysfunc(countw(&year_list)) %to 1 %by -1;
%let next_year = %scan(&year_list, &i);
Budget&next_year Spending&next_year Transfer&next_year
Use the macro in the middle of your RETAIN statement:
retain DEPT %varnames;
That should do it.