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.
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.
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.
Thank you Resa,, u rocks..
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
Thank you for response..
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
Thank you for response..
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.