topic Re: separate a string into two variables in SAS Programming

separate a string into two variables

fengyuwuzu — Tue, 06 Jul 2021 12:40:29 GMT

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;

Re: separate a string into two variables

Kurt_Bremser — Tue, 06 Jul 2021 13:02:15 GMT

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;

Re: separate a string into two variables

Astounding — Tue, 06 Jul 2021 13:02:19 GMT

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.

Re: separate a string into two variables

Amir — Tue, 06 Jul 2021 13:27:50 GMT

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.

Re: separate a string into two variables

fengyuwuzu — Tue, 06 Jul 2021 16:29:30 GMT

Thank you KurtBremser and Amir. Your suggestions both work.