How to transpose dataset?

Solved
Occasional Contributor
Posts: 9

How to transpose dataset?

I have a following dataset :

Name                  Product          CurrentAmount                PrevAmount

Mark                    prod1               5                                        2

Mark                     prod2              9                                        6

Mark                    prod3               1                                        0

Alex                     prod1               0                                        4

Alex                     prod2               23                                       0

Alex                     prod3               .                                          .

John                     prod1               45                                        24

John                     prod2               6                                          2

John                      prod3               15                                        13

How do I get the following result?

Name   Prod1_CurrentAmount       Prod1_PrevAmount        Prod2_CurrentAmount         Prod2_PrevAmount             Prod3_CurrentAmount      Prod3_PrevAmount

Mark                5                                   2                              9                                              6                                             1                              0

Alex                0                                    4                              23                                             0                                             .                              .

John                45                                   24                             6                                             2                                            15                             13                                    .

Thanks in advance for any help !

Accepted Solutions
Solution
‎07-31-2012 03:16 PM
Super Contributor
Posts: 1,636

Re: How to transpose dataset?

data have;
input Name :\$                 Product  : \$       CurrentAmount                PrevAmount;
cards;
Mark                    prod1               5                                        2
Mark                     prod2              9                                        6
Mark                    prod3               1                                        0
Alex                     prod1               0                                        4
Alex                     prod2               23                                       0
Alex                     prod3               .                                          .
John                     prod1               45                                        24
John                     prod2               6                                          2
John                      prod3               15                                        13
;
data temp;
set have;
by name notsorted;
if first.name then count=0;
count+1;
data want(drop=product cu: pre: count);
retain name Prod1_CurrentAmount Prod1_PrevAmount Prod2_CurrentAmount Prod2_PrevAmount Prod2_CurrentAmount   Prod3_PrevAmount;
set temp;
by name notsorted;
array _c(3) Prod1_CurrentAmount       Prod2_CurrentAmount       Prod3_CurrentAmount ;
array _p(3) Prod1_PrevAmount        Prod2_PrevAmount        Prod3_PrevAmount;
_c(count)=currentamount;
_p(count)=prevamount;
if last.name then do;
output;
call missing (of _c(*)_p(*));
end;
proc print;run;
Prod1_  Prod1_   Prod2_  Prod2_  Prod3_   Prod3_
Current   Prev   Current   Prev    Prev   Current
Obs  name   Amount  Amount   Amount  Amount  Amount   Amount

1   Mark      5       2        9       6       0        1
2   Alex      0       4       23       0       .        .
3   John     45      24        6       2      13       15

Linlin

All Replies
Solution
‎07-31-2012 03:16 PM
Super Contributor
Posts: 1,636

Re: How to transpose dataset?

data have;
input Name :\$                 Product  : \$       CurrentAmount                PrevAmount;
cards;
Mark                    prod1               5                                        2
Mark                     prod2              9                                        6
Mark                    prod3               1                                        0
Alex                     prod1               0                                        4
Alex                     prod2               23                                       0
Alex                     prod3               .                                          .
John                     prod1               45                                        24
John                     prod2               6                                          2
John                      prod3               15                                        13
;
data temp;
set have;
by name notsorted;
if first.name then count=0;
count+1;
data want(drop=product cu: pre: count);
retain name Prod1_CurrentAmount Prod1_PrevAmount Prod2_CurrentAmount Prod2_PrevAmount Prod2_CurrentAmount   Prod3_PrevAmount;
set temp;
by name notsorted;
array _c(3) Prod1_CurrentAmount       Prod2_CurrentAmount       Prod3_CurrentAmount ;
array _p(3) Prod1_PrevAmount        Prod2_PrevAmount        Prod3_PrevAmount;
_c(count)=currentamount;
_p(count)=prevamount;
if last.name then do;
output;
call missing (of _c(*)_p(*));
end;
proc print;run;
Prod1_  Prod1_   Prod2_  Prod2_  Prod3_   Prod3_
Current   Prev   Current   Prev    Prev   Current
Obs  name   Amount  Amount   Amount  Amount  Amount   Amount

1   Mark      5       2        9       6       0        1
2   Alex      0       4       23       0       .        .
3   John     45      24        6       2      13       15

Linlin

Occasional Contributor
Posts: 9

Re: How to transpose dataset?

Thanks Linlin!

I have a question : if for one of the names i have just prod1 and prod2,but array is defined for 3 . How do I deal with that?

It will put the missing value in that cell for prod3?

Lena

Super Contributor
Posts: 1,636

Re: How to transpose dataset?

You are welcome. Yes. the extra cells will have missing value.

Occasional Contributor
Posts: 9

Re: How to transpose dataset?

One more question  Linlin.

Could you  explain what doest it mean : call missing (of _c(*)_p(*));

Thanks a lot!

Super Contributor
Posts: 1,636

Re: How to transpose dataset?

It sets all the values from previous person to missing.

Contributor
Posts: 52

Re: How to transpose dataset?

proc sql;

create table changed_details as

select

Name,

Product,

propcase(Product) || "_CurrentAmount" as current_ID,

propcase(Product) || "_PrevAmount" as prev_ID,

CurrentAmount,

PrevAmount

from

original_data

group by

Name

order by

Name;

quit;

proc transpose data=changed_details out=current_ID (drop=_name_);

id current_ID;

var CurrentAmount;

by Name;

run;

proc transpose data=changed_details out=prev_ID (drop=_name_);

id prev_ID;

var PrevAmount;

by Name;

run;

data summary_data;

merge current_ID prev_ID;

by Name;

run;

I have not tested this. I hope it helps.

Occasional Contributor
Posts: 9

Re: How to transpose dataset?

It is also one of the solutions for this.

But why in CurrentID column I am getting this :  Prod2   _CurrentAmount   .There is a space in between.

And then in the Prev_Id dataset I am getting Prod2____PrevAmount in the header of the column.

Thanks!

Super Contributor
Posts: 1,636

Re: How to transpose dataset?

try changing

propcase(Product) || "_CurrentAmount" as current_ID,

to

catx('_',propcase(Product),'CurrentAmount') as current_ID,

Super User
Posts: 10,784

Re: How to transpose dataset?

OR IDGROUP, but maybe you need to change these variable name as you want.

proc means data=have noprint nway;

class name;

output out=want(drop=_ idgroup(out[3] ( currentamount PrevAmount)=);

run;

🔒 This topic is solved and locked.