How to transpose dataset?

[LenaS]

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 !

[Linlin · Accepted solution]

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

[LenaS]

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

[Linlin]

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

[LenaS]

One more question  Linlin.

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

Thanks a lot!

[Linlin]

It sets all the values from previous person to missing.

[tish]

How about this? You could replace the PROC SQL with a DATA step and a sort:

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.

[LenaS]

Tish thanks for your help!

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.

Can you advise ?

Thanks!

[Linlin]

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

   to

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

[Ksharp]

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;