Solved
Contributor
Posts: 25

# Catx or trim? Help with Problem

So I am trying to figure out how to take this string of data and seperate it out with a data step.  I understand the concept, but I am not sure how to apply it to multiple results.

My_Data: 12345 | Harry | Crumb | 123 Fake Street | Anytown | MA | More of the same

So I want to take the data and seperate it out:

Ac_Num = 12345

Fr_Nm = Harry

Ls_Nm = Crumb

City = Anytown

State = MA

Text = More of the Same

I know how to do this if there is 1 variable, so I am curious what I need to do to gather them all.

Ac_Num = substr(My_Data, 1, index(My_Data, '|') - 1);

Accepted Solutions
Solution
‎04-18-2017 06:34 AM
SAS Employee
Posts: 5

## Re: Catx or trim? Help with Problem

data finaldata;
set rawdata;
Ac_Num=trim(scan(my_data,1,'|'));
*scan(variable name, position, delimeter);
Fr_Nm=trim(scan(my_data,2,'|'));
Ls_Nm=trim(scan(my_data,3,'|'));
City=trim(scan(my_data,5,'|'));
State=trim(scan(my_data,6,'|'));
Text=trim(scan(my_data,7,'|'));
run;

All Replies
SAS Employee
Posts: 5

## Re: Catx or trim? Help with Problem

Is your raw data already in a sas table? If not what format is it in?

Contributor
Posts: 25

## Re: Catx or trim? Help with Problem

Yes it is all in 1 line on my table.

Contributor
Posts: 66

## Re: Catx or trim? Help with Problem

data ds1;
infile datalines dlm ="|";
input Ac_Num Fr_Nm \$ Ls_Nm \$ Address :\$50. City \$ State\$ Text :\$50. ;
datalines;
12345 | Harry | Crumb | 123 Fake Street | Anytown | MA | More of the same
run;

proc print data=ds1;
run;

Solution
‎04-18-2017 06:34 AM
SAS Employee
Posts: 5

## Re: Catx or trim? Help with Problem

data finaldata;
set rawdata;
Ac_Num=trim(scan(my_data,1,'|'));
*scan(variable name, position, delimeter);
Fr_Nm=trim(scan(my_data,2,'|'));
Ls_Nm=trim(scan(my_data,3,'|'));
City=trim(scan(my_data,5,'|'));
State=trim(scan(my_data,6,'|'));
Text=trim(scan(my_data,7,'|'));
run;

Contributor
Posts: 25

## Re: Catx or trim? Help with Problem

Thanks so much I haven't used the scan feature before, I am going to play around with that a little bit looks like it can help me in other programs as well.

SAS Employee
Posts: 5

## Re: Catx or trim? Help with Problem

Its similar to the substring function but is a bit more flexible I personally favor it. Good luck!

Super User
Posts: 9,799

## Re: Catx or trim? Help with Problem

Where has this data come from?  This is a key question here as it looks like your using delimited data, most likely from a delimited file.  If so, then write a datastep import program to read the data correctly, avoiding the need for post-processing the data at all.

Contributor
Posts: 25

## Re: Catx or trim? Help with Problem

Its actually how one of our tables are built.  Its an ugly process on how it is injested.

☑ This topic is solved.