BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Shayan2012
Quartz | Level 8

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,

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20

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

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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&...

 

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;

 

Shayan2012
Quartz | Level 8

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!

art297
Opal | Level 21

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

 

Shayan2012
Quartz | Level 8
Wow! This Macro is awesome. Thanks a lot for sharing this Arthur!
Ksharp
Super User
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

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 6 replies
  • 6470 views
  • 6 likes
  • 5 in conversation