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

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

9 REPLIES 9
Linlin
Lapis Lazuli | Level 10

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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

LenaS
Calcite | Level 5

One more question  Linlin.

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


Thanks a lot!

Linlin
Lapis Lazuli | Level 10

It sets all the values from previous person to missing.

tish
Calcite | Level 5

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
Calcite | Level 5

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
Lapis Lazuli | Level 10

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

   to

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 9 replies
  • 1071 views
  • 3 likes
  • 4 in conversation