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
Use the scan() function:
data want;
set have;
address1 = scan(address,1,'|');
address2 = scan(address,2,'|');
run;
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.
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
Use the scan() function:
data want;
set have;
address1 = scan(address,1,'|');
address2 = scan(address,2,'|');
run;
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
That's because I use the third argument to set the delimiter. Without the third argument, blanks are considered as delimiters.
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!
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.