BookmarkSubscribeRSS Feed
nketata
Obsidian | Level 7

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.

 

3 REPLIES 3
Astounding
PROC Star

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.

nketata
Obsidian | Level 7

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.

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 837 views
  • 0 likes
  • 2 in conversation