topic Re: How to collapse rows into columns in SAS Programming

How to collapse rows into columns

Shayan2012 — Wed, 09 Aug 2017 15:05:35 GMT

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,

Re: How to collapse rows into columns

PeterClemmensen — Wed, 09 Aug 2017 15:12:06 GMT

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

Re: How to collapse rows into columns

RW9 — Wed, 09 Aug 2017 15:18:58 GMT

First off I agree with @PeterClemmensen, 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&q=reshaping

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;

Re: How to collapse rows into columns

art297 — Wed, 09 Aug 2017 15:27:45 GMT

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

Re: How to collapse rows into columns

Shayan2012 — Wed, 09 Aug 2017 20:40:06 GMT

Wow! This Macro is awesome. Thanks a lot for sharing this Arthur!

Re: How to collapse rows into columns

Shayan2012 — Wed, 09 Aug 2017 20:42:18 GMT

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!

Re: How to collapse rows into columns

Ksharp — Thu, 10 Aug 2017 14:05:04 GMT

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