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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.