## Need help to repat the row based on column value.

Solved
Occasional Contributor
Posts: 10

# Need help to repat the row based on column value.

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.

Accepted Solutions
Solution
‎12-16-2011 03:26 AM
Frequent Contributor
Posts: 97

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

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.

All Replies
Solution
‎12-16-2011 03:26 AM
Frequent Contributor
Posts: 97

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

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.

Occasional Contributor
Posts: 10

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

Thank you Resa,, u rocks..

Super User
Posts: 10,784

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

```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

Occasional Contributor
Posts: 10

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

Thank you for response..

Super Contributor
Posts: 1,636

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

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

Occasional Contributor
Posts: 10

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

Thank you for response..

Contributor
Posts: 35

## Need help to repat the row based on column value.

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;

🔒 This topic is solved and locked.