Below are what data I have, and I want to separate the visit into two variables: the first one is "visit x" and the second one is "Screening Cycle x" or "Treatment Cycle x".
I wanted to use substr, but visit 10 and visit 11 have two digits and the rest visits have a single digit.
I also tried scan but it did not work; maybe I did not use it properly.
I wonder if someone can give some suggestions. Thanks
data test;
input visit $50.;
datalines;
Screening Cycle 1 Visit 1
Screening Cycle 2 Visit 2
Screening Cycle 3 Visit 3
Screening Cycle 4 Visit 4
Visit 5 Treatment Cycle 1
Visit 6 Treatment Cycle 2
Visit 7 Treatment Cycle 3
Visit 8 Treatment Cycle 4
Visit 9 Treatment Cycle 5
Visit 10 Treatment Cycle 6
Visit 11 Treatment Completion
;
run;
See this:
data test;
input visit $50.;
datalines;
Screening Cycle 1 Visit 1
Screening Cycle 2 Visit 2
Screening Cycle 3 Visit 3
Screening Cycle 4 Visit 4
Visit 5 Treatment Cycle 1
Visit 6 Treatment Cycle 2
Visit 7 Treatment Cycle 3
Visit 8 Treatment Cycle 4
Visit 9 Treatment Cycle 5
Visit 10 Treatment Cycle 6
Visit 11 Treatment Completion
;
data want;
set test;
length var1 var2 $25;
if index(visit,"Visit") = 1
then do;
var1 = catx(" ",scan(visit,1),scan(visit,2));
var2 = catx(" ",scan(visit,3),scan(visit,4),scan(visit,5));
end;
else do;
if countw(visit) = 4
then do;
var1 = catx(" ",scan(visit,3),scan(visit,4));
var2 = catx(" ",scan(visit,1),scan(visit,2));
end;
else do;
var1 = catx(" ",scan(visit,4),scan(visit,5));
var2 = catx(" ",scan(visit,1),scan(visit,2),scan(visit,3));
end;
end;
run;
Why would SUBSTR have a problem? Just select 8 characters. You get "Visit" plus a number, possibly with a trailing blank that shouldn't affect the end result.
Hi @fengyuwuzu,
Does the following give you what you want?
data have;
input visit $50.;
datalines;
Screening Cycle 1 Visit 1
Screening Cycle 2 Visit 2
Screening Cycle 3 Visit 3
Screening Cycle 4 Visit 4
Visit 5 Treatment Cycle 1
Visit 6 Treatment Cycle 2
Visit 7 Treatment Cycle 3
Visit 8 Treatment Cycle 4
Visit 9 Treatment Cycle 5
Visit 10 Treatment Cycle 6
Visit 11 Treatment Completion
;
data want;
set have;
length
visit_info $ 50
cycle_info $ 50
;
if visit eq: 'Visit' then
visit_info = catx(' ','Visit',scan(visit,2));
else
visit_info = catx(' ','Visit',scan(visit,-1));
cycle_info = strip(tranwrd(visit,strip(visit_info),''));
run;
Thanks & kind regards,
Amir.
Thank you KurtBremser and Amir. Your suggestions both work.
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.