BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
IgawaKei29
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
jessicaking
SAS Employee


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

8 REPLIES 8
jessicaking
SAS Employee

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

IgawaKei29
Quartz | Level 8

Yes it is all in 1 line on my table.  

atul_desh
Quartz | Level 8

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
SAS Employee


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
Quartz | Level 8

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
SAS Employee

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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