Catx or trim? Help with Problem

[IgawaKei29]

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

Address = 123 Fake Street

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);

[jessicaking]

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

[IgawaKei29]

Yes it is all in 1 line on my table.  

[atul_desh]

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;

[jessicaking · Accepted solution]

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,'|'));
Address=trim(scan(my_data,4,'|'));
City=trim(scan(my_data,5,'|'));
State=trim(scan(my_data,6,'|'));
Text=trim(scan(my_data,7,'|'));
run;

[IgawaKei29]

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.

[jessicaking]

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

[RW9]

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.

[IgawaKei29]

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