topic Re: Need help to repat the row based on column value. in SAS Programming

Need help to repat the row based on column value.

Rohan — Fri, 16 Dec 2011 05:40:30 GMT

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.

Re: Need help to repat the row based on column value.

Resa — Fri, 16 Dec 2011 08:26:16 GMT

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 ;

run ;

I hope this helps.

Re: Need help to repat the row based on column value.

Ksharp — Fri, 16 Dec 2011 09:34:29 GMT

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

Re: Need help to repat the row based on column value.

Linlin — Fri, 16 Dec 2011 12:16:13 GMT

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

Re: Need help to repat the row based on column value.

Rohan — Tue, 27 Dec 2011 06:57:33 GMT

Thank you Resa,, u rocks..

Re: Need help to repat the row based on column value.

Rohan — Tue, 27 Dec 2011 06:59:04 GMT

Thank you for response..

Re: Need help to repat the row based on column value.

Rohan — Tue, 27 Dec 2011 06:59:22 GMT

Thank you for response..

Need help to repat the row based on column value.

Saugata — Thu, 29 Dec 2011 12:31:47 GMT

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;