How to split one column into two based on a delimiter?

[Nafin]

Hello SAS Community,

I have one column with addresses and a delimiter which I would like to split into two columns based on the delimiter |.

The data I have looks like this:

Addresses I have	Addresses I want	More Addresses I want
1 Dexter Ln	1 Dexter Ln
87 N Main Ave | Suite B	87 N Main Ave	Suite B
46 Kelly Ave | Apt 4	46 Kelly Ave	Apt 4
616 Park Ave	616 Park Ave
243 Morris St | #B64	243 Morris St	#B64

Thank you

[japelin]

Hi, Nafin

how about this

data address;
  length address $200;
  infile datalines truncover;
  input address $ 1-200;
datalines;
1 Dexter Ln
87 N Main Ave | Suite B
46 Kelly Ave | Apt 4
616 Park Ave
243 Morris St | #B64
;
run;

data address2;
  set address;
  if find(address,'|')>0 then do;
    address1=strip(substr(address,1,find(address,'|')-1));
    address2=strip(substr(address,find(address,'|')+1));
  end; else
  do;
    address1=address;
    address2='';
  end;
run;

Although premise that delimiter is only one.

[Nafin]

Thank you Kawakami! It works, however, I noticed that my dataset contain some records with more than one delimiter.

Thank you for your help though.   

Nafin

[Kurt_Bremser · Accepted solution]

Use the scan() function:

data want;
set have;
address1 = scan(address,1,'|');
address2 = scan(address,2,'|');
run;

[Nafin]

Thank you KurtBremser! It works! 

Although your code works perfectly, I'm little puzzled because I tried the Scan function before and it didn't work for me. When I used it, it allowed me to copy over only a single word to the new column "Address1" (and I put 1 in the second argument within the parenthesis in the scan function exactly like you).

How come your code allows to copy to the new column "Address1" all the words that are positioned before the delimiter even though you use 1 in the second argument of the scan function?

Thank you!

Nafin

[Kurt_Bremser]

That's because I use the third argument to set the delimiter. Without the third argument, blanks are considered as delimiters.

[Nafin]

Thank you for the clarification!

Nafin

[jahunter]

This worked really well! I need to extend the idea. I have a field where I don't know the number of delimited values (in one case up to 800 times).

In my case, I have a column named "Group":

 - Row 1 has the values of "a, x, r, f"

 - Row 2 has the values of "r, b, a, q, g, 8"

 - Row 3 has the value of "s"

I'd like it to create:

Row 1 New Column of Group_1 with the value of "a"

Row 1 New Column of Group_2 with the value of "x"

Row 1 New Column of Group_3 with the value of "r"

Row 1 New Column of Group_4 with the value of "f"

.....

Row 3 column created above Group_1 with the value of "s" and all other New Columns would be blank.

I've been trying a "do" statement using the scan to iterate up the Goup_# and then use it again as the scan number, but it's alluding me. 🙂

Thanks in advance!

[Kurt_Bremser]

If you have an arbitrary number of values, it is best to split vertically.

Create a single variable, and output for every item. Keep the new variable, any variables you need for identification, and the counter.

A long dataset is always easier to use than a wide one.

[ed_sas_member]

Hi @Nafin 

Here is an alternative to the scan function.

Address1: looks for the pattern "one or many spaces followed by a | and then any character at the end of the string (\s+\|.*$)" and replace it by nothing

Address2: look for either:

- a string without symbol | ([^\|]*)

- the following pattern: any character at the beginning of the string followed by a | and then one or more spaces (^.*\|\s+)

data want;
	set address;
	address1 = prxchange('s/\s*\|[^\|]*$//',1,address);
	address2 = prxchange('s/^[^\|]*\|\s*|[^\|]*//',1,address);
run;

[Nafin]

Thank you ed_sas_member for the code and explanation! This method works.

Nafin



Thank you for your help though.   

Nafin

[ed_sas_member]

Hi @Nafin 

You're welcome