Transpose data

[Reeza]

This code is far from optimal, but I think you'll understand it and should be able to modify and extend it if necessary. 

It would help if you posted data that was closer to your actual problem, or state the limitations up front. 

It's based on @RW9 solution above, but extended to account for unknown number of variables per variable type. 

Untested, since there's no sample data and I'm too lazy to make any. 

data want;
  set have;
  array vol_{*}; *Use asterisk to indicate unknown dimension, uses all variables starting with vol_;
  array rev_{*};
  array profit_{*};
  array sp_{*};

max_counter = max(dim(vol_), dim(rev_), dim(profit_), dim(sp_));

  do i=1 to max_counter;
   call missing(volume, revenue, profit, spend); *Set to missing at top of array;
   *Assign if value exists, otherwise it stays missing; 
   if i <= dim(vol_) then volume=vol_(i);
    if i <= dim(rev_) then revenue=rev_(i);
    if i <= dim(profit_) then profit=prof_(i);
    if i <= dim(sp_) then spend=sp_(i);
    output;
  end;


run;

[ramchinna24]

I gave sample  data please check. still i am getting same reults: please verify it. dono how to put missing for other observations

data have;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4 sp_5;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 33
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 12
;
run;

[Reeza]

---

@ramchinna24 wrote:

still i am getting same reults: please verify it. dono how to put missing for other observations

What does that mean? I don't know what same results means. The code below works for me, I'm leaving the rest up to you.

data have;
input Item Category $ Subcategory $ Vol_1 Vol_2 Vol_3 Rev_1 Rev_2 Rev_3 Profit_1 Profit_2 Profit_3 SP_1 SP_2 SP_3 sp_4 sp_5;
cards;
1 ABC cv 2 3 4 10 34 55 12 12 11 20 21 22 23 33
2 xyz xc 4 6 7 23 44 66 14 15 12 23 23 25 26 12
;
run;

data want;
  set have;
  array vol_{*} vol_:; *Use asterisk to indicate unknown dimension, uses all variables starting with vol_;
  array rev_{*} rev_:;
  array profit_{*} profit_:;
  array sp_{*} sp_:;

max_counter = max(dim(vol_), dim(rev_), dim(profit_), dim(sp_));

  do i=1 to max_counter;
   call missing(volume, revenue, profit, spend); *Set to missing at top of array;
   *Assign if value exists, otherwise it stays missing; 
   if i <= dim(vol_) then volume=vol_(i);
    if i <= dim(rev_) then revenue=rev_(i);
    if i <= dim(profit_) then profit=profit_(i);
    if i <= dim(sp_) then spend=sp_(i);
    
    If I>1 then call missing(category, subcategory);

    Output;
  end;

drop vol_: rev_: profit_: sp_:;

run;