topic HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS in SAS Programming

HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

nketata — Tue, 26 Jul 2016 17:12:35 GMT

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.

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

Astounding — Tue, 26 Jul 2016 18:32:14 GMT

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.

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

nketata — Tue, 26 Jul 2016 18:37:25 GMT

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.

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

Astounding — Tue, 26 Jul 2016 19:08:16 GMT

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;

%mend varnames;

Use the macro in the middle of your RETAIN statement:

retain DEPT %varnames;

That should do it.