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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.