topic Re: How do I separate this variable into 2 variables? in SAS Programming

How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 14:19:26 GMT

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:

Re: How do I separate this variable into 2 variables?

PaigeMiller — Wed, 22 Mar 2023 14:27:58 GMT

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;

Re: How do I separate this variable into 2 variables?

yabwon — Wed, 22 Mar 2023 14:28:55 GMT

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

Re: How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 14:29:13 GMT

This worked perfectly. Thanks, PaigeMiller!

Re: How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 14:30:07 GMT

Brilliant! Thanks, Bart, that works also!

Re: How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 16:56:34 GMT

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;

Re: How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 17:02:54 GMT

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.

Re: How do I separate this variable into 2 variables?

yabwon — Wed, 22 Mar 2023 17:04:15 GMT

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

Re: How do I separate this variable into 2 variables?

Hello_there — Wed, 22 Mar 2023 17:23:06 GMT

Thanks, Bart!