DATA Step, Macro, Functions and more

Catx or trim? Help with Problem

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-18-2017 06:34 AM
Occasional Contributor
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,'|'));
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;

View solution in original post


All Replies
Occasional Contributor
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: 23

Re: Catx or trim? Help with Problem

Yes it is all in 1 line on my table.  

Contributor
Posts: 60

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
Occasional Contributor
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,'|'));
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;

Contributor
Posts: 23

Re: Catx or trim? Help with Problem

Thanks so much Smiley Happy 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.

Occasional Contributor
Posts: 5

Re: Catx or trim? Help with Problem

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

Super User
Super User
Posts: 7,401

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: 23

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 159 views
  • 2 likes
  • 4 in conversation