Transpose two or more variables in one step

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Transpose two or more variables in one step

Hi,

I have a long dataset (like this) that I want to transpose into a wide (one subject per row). It is possible to do this in one step?

Example:

id      date              atccode

1      01jun2013      A03BA02

1      06jul2013      C10AA01

2     12jan2012      B02CA01

2     01mar2013     C10AA02

2     14aug2013     C07BB02

I want the result to be like this:

id        date1           date2            date3         atccode1      atccode2      atccode3

1        01jun2013     06jul2013      .                A03BA02      C10AA01      .

2        12jan2012    01mar2013    14aug2013  B02CA01      C10AA02     C07BB02

I can do it in two proc transpose steps(and merge the results) but is it possible to do in one? Can I do this with an array?

Thanks

Thomas


Accepted Solutions
Solution
‎10-16-2013 03:42 PM
PROC Star
Posts: 7,474

Re: Transpose two or more variables in one step

Posted in reply to bollibompa

If you know the maximum number of groups (e.g., in your example, 3), then you can do it in one pass using proc summary.  e.g.:

proc summary data=have nway;

   class id;

   output out=want (drop=_Smiley Happy idgroup(out[3](date: atccodeSmiley Happy=);

   run;

If you don't know the maximum number of groups, you can preface the run with a short proc sql run.  e.g.:

proc sql noprint;

   select max(idcount) into :dim

      from (select count(id) as idcount from have group by id);

   quit;

   run;

proc summary data=have nway;

   class id;

   output out=want (drop=_Smiley Happy idgroup(out[&dim](date: atccodeSmiley Happy=);

   run;

p.s.  the idea was really first (I think) mentioned by datanull.

View solution in original post


All Replies
Super Contributor
Posts: 334

Re: Transpose two or more variables in one step

Posted in reply to bollibompa

I think technically even through a datastep  it would be two steps since you would have to pass through once to get the max number of dates an id had before you can setup your datastep transpose.

EJ

Solution
‎10-16-2013 03:42 PM
PROC Star
Posts: 7,474

Re: Transpose two or more variables in one step

Posted in reply to bollibompa

If you know the maximum number of groups (e.g., in your example, 3), then you can do it in one pass using proc summary.  e.g.:

proc summary data=have nway;

   class id;

   output out=want (drop=_Smiley Happy idgroup(out[3](date: atccodeSmiley Happy=);

   run;

If you don't know the maximum number of groups, you can preface the run with a short proc sql run.  e.g.:

proc sql noprint;

   select max(idcount) into :dim

      from (select count(id) as idcount from have group by id);

   quit;

   run;

proc summary data=have nway;

   class id;

   output out=want (drop=_Smiley Happy idgroup(out[&dim](date: atccodeSmiley Happy=);

   run;

p.s.  the idea was really first (I think) mentioned by datanull.

Contributor
Posts: 73

Re: Transpose two or more variables in one step

Thanks for helpful answers!

i am just curious but could is also be possible to do this using an array?

/Thomas

PROC Star
Posts: 7,474

Re: Transpose two or more variables in one step

Posted in reply to bollibompa

Quick answer: Yes it can be done using arrays!  A macro that uses that approach can be found at:

A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

The reason we wrote the macro, in the first place, was so that we didn't have to write customized code each time someone wanted to do something like this.  However, since you've asked, here is actual code that does the task using arrays:

data have;

  informat date date9.;

  format date date9.;

  input id date atccode $;

  cards;

1      01jun2013      A03BA02

1      06jul2013      C10AA01

2     12jan2012      B02CA01

2     01mar2013     C10AA02

2     14aug2013     C07BB02

;

data want (drop=i date atccode);

  set have;

  by id;

  array wantnum(3) date1-date3;

  array wantchar(3) $ atccode1-atccode3;

  retain wantnum wantchar;

  format date1-date3 date9.;

  if first.id then do;

    call missing(of wantnum(*));

    call missing(of wantchar(*));

    i=0;

  end;

  i+1;

  wantnum(i)=date;

  wantchar(i)=atccode;

  if last.id then output;

run;

Contributor
Posts: 73

Re: Transpose two or more variables in one step

Thanks for all helpful answers/Thomas

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 266 views
  • 0 likes
  • 3 in conversation