Need help to repat the row based on column value.

[Rohan]

Hello All,

I have a problem. I have to split the row based on a column value.

My data is like :

Key  Coupon   ID

1       1234      A

2        0100     B

3.       0081     C

Otput should be

Coupon      ID

1               A

2               A    

3               A    

4               A              

1               B

8               C

1               C

ie. if for all the non-zero values in Column Coupon, the entire row should repeat except Column Coupon. The Vaue in coupon should be splied in 1 char.

Please help me. I'm new for SAS programming.

[Resa · Accepted solution]

Of course there are several ways to skin a cat, but this is what I can suggest.

The first part of the code is to "rebuilt" your input dataset in which I assumed that coupon is not necessarily limited to 4 characters (I set the length to 20)

The second part creates the output dataset. Basic principle is to go through the coupon content (renamed since the output has a different length then the original) character by character and write a record to the output dataset if the content is not equal to 0.

data have ;

     length key 8 coupon $20 ID $1 ;

     input key coupon $ ID $ ;

     datalines ;

1 1234 A

2 0100 B

3 0081 C

;

run ;

data want (keep=coupon ID) ;

     length coupon $1 ID $1 ;

     set have (rename=(coupon=old_coupon)) ;

     do i=1 to length(strip(old_coupon)) ;

          if substr(old_coupon,i,1)^='0' then do ;

               coupon = substr(old_coupon,i,1) ;

               output ;

          end ;

     end ;

run ;

I hope this helps.

[Rohan]

Thank you Resa,, u rocks..

[Ksharp]

How about:

data have ;
     length key 8 coupon $20 ID $1 ;
     input key coupon $ ID $ ;
     datalines ;
1 1234 A
2 0100 B
3 0081 C
;
run ;
data want(keep=_coupon id);
set have;
j=0; 
do until(j=0);
j=findc(coupon,'123456789',j+1);
if j ne 0 then do;_coupon=substr(coupon,j,1);output;end;
end;
run;

Ksharp

[Rohan]

Thank you for response..

[Linlin]

data have;

input Key  Coupon $ ID $;

cards;

1      1234     A

2      0100     B

3      0081     C

;

data want(keep=_c id rename=(_c=coupon));

  length _c $ 1;

  set have;

  c=compress(coupon,'123456789','k');

  do i=1 to length(c);

  _c=substr(c,i,1);

  output;

  end;

run;

proc print;run;

Linlin

[Rohan]

Thank you for response..

[Saugata]

I may be late in responding, but you can try the below code also:

data have ;

    length key 8 coupon $20 ID $1 ;

            input key coupon $ ID $ ;

    datalines ;

                        1 1234 A

                        2 0100 B

                        3 0081 C

;

run ;

data want (keep= coupon Id);  

            set have;

            coupon2=compress(compress(coupon,0));

            i=1;

            do while (substr(coupon2,i,1) ne '');

                        coupon=substr(coupon2,i,1);

                        output;

                        i+1;

            end;

run;