topic Re: How to split one column into two based on a delimiter? in SAS Programming

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

Nafin — Fri, 13 Dec 2019 04:33:42 GMT

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

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

japelin — Fri, 13 Dec 2019 05:08:55 GMT

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.

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

Kurt_Bremser — Fri, 13 Dec 2019 07:16:55 GMT

Use the scan() function:

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

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

ed_sas_member — Fri, 13 Dec 2019 09:08:22 GMT

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;

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

Nafin — Sat, 14 Dec 2019 06:09:38 GMT

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

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

Nafin — Sat, 14 Dec 2019 06:13:05 GMT

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

Nafin

Thank you for your help though.

Nafin

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

Nafin — Sat, 14 Dec 2019 06:31:38 GMT

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

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

Kurt_Bremser — Sat, 14 Dec 2019 07:20:00 GMT

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

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

ed_sas_member — Sat, 14 Dec 2019 09:31:29 GMT

Hi @Nafin

You're welcome

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

Nafin — Mon, 16 Dec 2019 14:22:12 GMT

Thank you for the clarification!

Nafin

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

jahunter — Wed, 10 Feb 2021 00:51:14 GMT

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!

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

Kurt_Bremser — Wed, 10 Feb 2021 07:34:07 GMT

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.