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.
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:
proc sql;
select distinct year into : year_list separated by ' ' from A;
quit;
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.
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.
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.
%macro varnames;
%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
%end;
%end;
%mend varnames;
Use the macro in the middle of your RETAIN statement:
retain DEPT %varnames;
That should do it.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.