DATA Step, Macro, Functions and more

How to collapse rows into columns

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

How to collapse rows into columns

Assume that I have a dataset like this:

data execs;
    input id $ firmid $ year;
    datalines;                      
         A001 APL  1990
         A002 MSFT 1993
         A002 AMZN 1994
         A002 APL  1996
         A003 CSCO 1994
;
run;

This hypothetical table contains the first time a person has joined the firm. For example, Mr A002 has joined MSFT in 1993, and then joined AMZN in 1994 and then joined APL in 1996. Mr A003 only joined CSCO in 1994.

 

I want to collapse this data into person-level shape, so that each row is for a person, with multiple columns for every job experience that the person has had.

data want;
   input id $ firmid $ year $ firmid2 $ year2 $ firmid3 $ year3;
   datalines;                      
    A001 APL  1990 . . . .
    A002 MSFT 1993 AMZN 1994 APL 1996
    A003 CSCO 1994 . . . .
;
run;

Is there a nice way to do this procedure? I grappled with Proc Transpose but it does not seem to give me the desired output, or I cannot figure it out. Any helps?

 

Thanks a lot,

 


Accepted Solutions
Solution
2 weeks ago
PROC Star
Posts: 7,366

Re: How to collapse rows into columns

I have a macro that simplifies the task of making a wide file wider. You can download it at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

For your current problem you would only have to call the macro with the following code:

%transpose(data=execs, out=want, by=id, var=firmid year)

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
PROC Star
Posts: 554

Re: How to collapse rows into columns

Why do you want your data like this? In my opinion your execs dataset looks way better than your want dataset.

 

 

Super User
Super User
Posts: 7,430

Re: How to collapse rows into columns

First off I agree with @draycut, from a programming point of view your first dataset is far simpler to work with.

Now what you want to do is called transposing or reshaping data.  Your going from a normalised (data goes down, common in programming, and in databasing) to transposed data (common in Excel).  To do this there is multiple resources out there which can show you the proc transpose for simple data transposing, or by using arrays for more complex mapping.  For instance from this forum:

https://communities.sas.com/t5/forums/searchpage/tab/message?advanced=false&allow_punctuation=false&...

 

In your case it looks like a more complicate transpose, so you would need arrays:

/* assumes max 3 firms *.
data want;
  set execs;
  by id;
  array firm_id{3} $20;
  array year_{3} $20;
  retain firm_id: year_:;
  if first.id then do;
    ind=1;
    call missing(of firm_id:,of year_:);
  end;
  firm_id{ind}=firmid;
  year_{ind}=year;
  ind=ind+1;
  if last.id then output;
run;

 

Frequent Contributor
Posts: 75

Re: How to collapse rows into columns

Thanks a lot @draycut and @RW9. Yes I do agree that for programming the long version is much better. I needed to screen data by eyes before going further and I didn't want to do it clumsilily by excel, so I thought I might learn something this way, as I did from RW9s code.

 

Thanks!

Solution
2 weeks ago
PROC Star
Posts: 7,366

Re: How to collapse rows into columns

I have a macro that simplifies the task of making a wide file wider. You can download it at: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

 

For your current problem you would only have to call the macro with the following code:

%transpose(data=execs, out=want, by=id, var=firmid year)

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 75

Re: How to collapse rows into columns

Wow! This Macro is awesome. Thanks a lot for sharing this Arthur!
Super User
Posts: 9,691

Re: How to collapse rows into columns

data execs;
    input id $ firmid $ year;
    datalines;                      
         A001 APL  1990
         A002 MSFT 1993
         A002 AMZN 1994
         A002 APL  1996
         A003 CSCO 1994
;
run;
proc sql noprint;
select max(n) into : n
 from (select count(*) as n from execs group by id);
 quit;
proc summary data=execs;
by id;
output out=want idgroup(out[&n] (firmid year)=);
run;

 

Or use MERGE skill.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 128 views
  • 6 likes
  • 5 in conversation