topic Create separate rows based on the second/ third space in SAS Programming

Create separate rows based on the second/ third space

Feyng819 — Tue, 05 Jul 2022 06:27:05 GMT

For example I have the data:
data have;
input name$;
cards;
Aaron Smith Andrew Knoi
Linda Jackson Betty Young
;
run;

I want to create separate rows based on the second space like the data in the following.
data want;
input name$;
cards;
Aaron Smith
Andrew Knoi
Linda Jackson
Betty Young
;
run;

How can create separate rows based on the second space? I tried to use scan but it seems to be only working for the first space. Thank you!

Re: Create separate rows based on the second/ third space

Kurt_Bremser — Tue, 05 Jul 2022 08:05:50 GMT

Loop through the string, incrementing the loop counter by 2:

data have;
input name $80.;
cards;
Aaron Smith Andrew Knoi
Linda Jackson Betty Young
;

data want;
set have;
i = 1;
word = scan(name,i);
do while(word ne "");
  n_name = catx(" ",scan(name,i),scan(name,i+1));
  output;
  i = i + 2;
  word = scan(name,i);
end;
keep n_name;
rename n_name=name;
run;

Re: Create separate rows based on the second/ third space

Astounding — Tue, 05 Jul 2022 11:11:13 GMT

It's actually easier if you create two variables instead of one:

data have;
  length first_name last_name $ 20;
  input first_name last_name @@;
cards;
Aaron Smith Andrew Knoi
Linda Jackson Betty Young
;

Re: Create separate rows based on the second/ third space

andreas_lds — Tue, 05 Jul 2022 11:15:29 GMT

Can you add an example showing what should happen if the value has to separated at the third space?

Re: Create separate rows based on the second/ third space

Ksharp — Tue, 05 Jul 2022 11:57:06 GMT

data have;
input name $40.;
cards;
Aaron Smith Andrew Knoi
Linda Jackson Betty Young
;
run;

data want;
 set have;
 call scan(name,2,p,l,' ');
 new_name=left(substr(name,1,p+l));output;
 new_name=left(substr(name,p+l));output;
 drop p l;
run;

Re: Create separate rows based on the second/ third space

Feyng819 — Tue, 05 Jul 2022 15:07:15 GMT

This works great! Thank you