Hello
I have a string variable which contains letters and ';' signs. for example: A;B;A;A;B;C
The length of these strings is not constant and varies by observations, so the next observation may be something like A;B or even a single B
I need to create variables to store each letter separately. for example for the first string, I need 6 separate variables, say alpha1, alpha2, alpha3, alpha4, alpha5 and alpha6 which each of them only contains one of the 6 letters appeared in the string. so alpha1 will be A, alpha2 will be B, alpha3 will be A and so on
For the second string, I will only need alpha1 to store A and alpha2 to storeB and finally for the third string, I just need alpha1 to store B and all other alpha variables to be empty.
My data base contains about 1 million observations so having a lot of if,then statements may not be the smartest solution.
Any suggestion is greatly appreciated!
Something like this?
data have;
input var $char20.;
CARDS4;
A;B;C;D;A;B
A;B;C
B;A;D;D
B
J;K;C;K;J;K;A;B;C;D
;;;;
proc sql;
select (max(length(var))+1)/2 into :vn from have;quit;
data want;
array alpha(&vn) $1.;
set have;
do i=1 to dim(alpha) until (missing (alpha(i)));
alpha(i)=scan(var,i);
end;
drop i;
run;
proc print;run;
Haikuo
Something like this?
data have;
input var $char20.;
CARDS4;
A;B;C;D;A;B
A;B;C
B;A;D;D
B
J;K;C;K;J;K;A;B;C;D
;;;;
proc sql;
select (max(length(var))+1)/2 into :vn from have;quit;
data want;
array alpha(&vn) $1.;
set have;
do i=1 to dim(alpha) until (missing (alpha(i)));
alpha(i)=scan(var,i);
end;
drop i;
run;
proc print;run;
Haikuo
Wow! It worked beautifully! Thank you very much
I am jealous of your skills !
HaiKuo,
countw() is a good way.
do i=1 to countw(var,';');
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.