topic Re: How to transpose dataset? in SAS Procedures

How to transpose dataset?

LenaS — Tue, 31 Jul 2012 18:53:35 GMT

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 !

Re: How to transpose dataset?

Linlin — Tue, 31 Jul 2012 19:16:23 GMT

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

Re: How to transpose dataset?

tish — Tue, 31 Jul 2012 19:17:42 GMT

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.

Re: How to transpose dataset?

LenaS — Tue, 31 Jul 2012 19:55:56 GMT

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

Re: How to transpose dataset?

Linlin — Tue, 31 Jul 2012 20:01:34 GMT

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

Re: How to transpose dataset?

LenaS — Tue, 31 Jul 2012 20:01:47 GMT

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!

Re: How to transpose dataset?

LenaS — Tue, 31 Jul 2012 20:04:50 GMT

One more question Linlin.

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

Thanks a lot!

Re: How to transpose dataset?

Linlin — Tue, 31 Jul 2012 20:09:51 GMT

try changing

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

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

Re: How to transpose dataset?

Linlin — Tue, 31 Jul 2012 20:11:32 GMT

It sets all the values from previous person to missing.

Re: How to transpose dataset?

Ksharp — Wed, 01 Aug 2012 04:52:12 GMT

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;