Code to move values from one column to 3 category columns

[Moraes86]

Hi guys,

I am trying to move the values from the column "lot" to 3 columns A, B and C, according to respective category (A, B or C)

The second table is my goal

You can see the image below with 2 tables.  The second table is what I am trying to reach out, because I would like to move variable from column lot to category A, B or C.

Thanks guys,

Daniel

[sbxkoenk · Accepted solution]

Hello,

Very strange transpose, but OK ;-).

Please always provide your 'HAVE' data as a datastep with cards (datalines).

You get much more responses then!!

Code below works but better, more performant and more elegant code, is certainly possible.

data have;
 Length Head $ 20 Lot $ 10;
 infile cards delimiter='|';
 input Head $ Lot $ A B C;
 total=SUM(A,B,C);
 cards;
S001, S012, S013 |R8091120 |. |. |2
S001, S012, S013 |Y0661220 |. |. |2
S003             |P8690321 |1 |. |.
S004             |P3030321 |2 |. |.
S004             |P3440221 |2 |. |.
;
run;

data want(drop=Aold Bold Cold);
 set have(rename=(A=Aold B=Bold C=Cold));
 if      Aold ne . then A=Lot;
 else if Bold ne . then B=Lot;
 else if Cold ne . then C=Lot;
run;

data want(drop=Aold Bold Cold Lot lagHead);
 LENGTH Head $ 20 A $ 50 B $ 50 C $ 50;
 set want(rename=(A=Aold B=Bold C=Cold));
 retain A B C;
 lagHead=lag(Head);
 by Head;
 if first.Head then do; A=Aold; B=Bold; C=Cold; end;
 if NOT first.Head AND Head=lagHead then do; 
  A=strip(A)||','||strip(Aold); if A=',' then A='';
  B=strip(B)||','||strip(Bold); if B=',' then B='';
  C=strip(C)||','||strip(Cold); if C=',' then C='';
 end;
 if last.Head then output;
run;
/* end of program */

Cheers,

Koen

[Tom]

It is not clear what TOTAL means in your example output.  If I take the MAX over all A,B,C for all observations for HEAD I get the same number you reported for that sample data.

You need to make NEW variables since the old A,B,C are numeric and the new A,B,C are character.  Make them long enough to hold all possible lists.

So if the data is sorted by HEAD it is pretty straight forward.  For only three variables arrays might be overkill but here is an example.

data want ;
   do until (last.head);
     set have ;
     by head ;
     array _num a b c ;
     array _ch $100 _a _b _c ;
     do i=1 to dim(_num);
       if not missing(_num[i]) then do;
         _ch[i]=catx(', ',_ch[i],lot);
         total = max(total,_num[i]);
       end;
     end;
   end;
   drop i a b c lot ;
   rename _a=A _b=B _c=C ;
run;

Results:

Obs    Head                A                     B            C             total

 1     S001, S012, S013                               R8091120, Y0661220      2
 2     S003                P8690321                                           1
 3     S004                P3030321, P3440221                                 2

[Moraes86]

Thanks you soo much for this code. 

[Moraes86]

Thank you so much for this code. 

It worked and it will help me a lot.

Daniel