How do I separate this variable into 2 variables?

[Hello_there]

Hi, I'm trying to separate this one variable into a 2 variables called pre and post, and i don't understand why i'm having difficulty in doing this using the scan function.

data have1; 
infile datalines dsd dlm=",";
length shift $200;
	input shift $;
datalines;
stable to stable,
stable to unstable,
unstable to stable,
zero (empty) to +1 (ok),
+1 (ok) to +2 (good)
;
run;


data want; set have1;
pre=scan(shift, 1, " to ");
post=scan(shift, 2, " to ");
run;

 When I use my code for the data step want, i get this, which is what i don't want:

[PaigeMiller · Accepted solution]

SCAN uses the letters in the third argument to split words. Your third argument contains the letter 't', and so on line one, the first "word" ends when a 't' is found, which means the first "word" is 's' (from the text string 'stable to stable');

How about this code:

data want; 
    set have1;
    where=find(shift,' to ');
    pre=substr(shift,1,where-1);
    post=substr(shift,where+4);
    drop where;
run;

[Hello_there]

This worked perfectly. Thanks, PaigeMiller!

[Hello_there]

Hi @PaigeMiller , do you know i would account for if there is more than 1 " to ", and i just want what's after the last one?

The last value has 2 " to ".

data have1; 
infile datalines dsd dlm=",";
length shift $200;
	input shift $;
datalines;
stable to stable,
stable to unstable,
unstable to stable,
zero (empty) to +1 (ok),
+1 (ok) to +2 (good)
zero (good to good) to +3 (great)
;
run;


data want; 
    set have1;
    loc=find(shift,' to ');
    pre=substr(shift,1,loc-1);
    post=substr(shift,loc+4);
run;

[yabwon]

loc=find(shift,' to ', -(lengthn(shift)));

[Hello_there]

Thanks, Bart!

[Hello_there]

Actually, never mind, there's another variable in my data set that will help me so i don't have to parse this text anymore than i have to. Ignore my previous reply.

[yabwon]

Scan() separates over a single character. Try this:

data have1; 
infile datalines dsd dlm=",";
length shift $200;
	input shift $;
datalines;
stable to stable,
stable to unstable,
unstable to stable,
zero (empty) to +1 (ok),
+1 (ok) to +2 (good)
;
run;


data want; 
set have1;
shift1=tranwrd(shift, " to ", "#");
pre=scan(shift1, 1, "#");
post=scan(shift1, 2, "#");
run;
proc print;
run;

Bart

[Hello_there]

Brilliant! Thanks, Bart, that works also!