DATA Step, Macro, Functions and more

HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

Reply
Contributor
Posts: 35

HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

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=_Smiley Happy 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=_Smiley Happy

idgroup(out[7] (la ln)=) / autoname;

run;

 

Thanks.

 

Super User
Posts: 5,499

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

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.

Contributor
Posts: 35

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

Posted in reply to Astounding

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.

Super User
Posts: 5,499

Re: HOW TO REORDER VARIABLES BASED ON 2 PARAMETERS

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.

Ask a Question
Discussion stats
  • 3 replies
  • 219 views
  • 0 likes
  • 2 in conversation