BookmarkSubscribeRSS Feed
HarryT
Calcite | Level 5

I have imported the file and kept only the Text column, which is made up of a text blob. One row is pasted below. I want to move the numbers after the word 'ORIG:' in bold below to there own data set so i can count how many characters they are. 

 

Any help will be appreciated.

 

 

WIRE TYPE:BOOK OUT DATE:013018 TIME:1716 ET                 TRN:2018013000426338                                        FED IMAD:                                                   RELATED REF:9796E9BF1D3F7440                                ORIG:ECE2D1F0F6D04FEC80363791807F ID:                       BNF:      BNF BK: ID:                                                 INT BK: ID:                                                 RECV BK: ID:                                                PAYMENT DETAILS:

6 REPLIES 6
art297
Opal | Level 21

Here is one way:

data want;
  informat orig $80.;
  input @"ORIG:" orig;
  orig_length=length(orig);
  cards;
WIRE TYPE:BOOK OUT DATE:013018 TIME:1716 ET                 TRN:2018013000426338                                        FED IMAD:                                                   RELATED REF:9796E9BF1D3F7440                                ORIG:ECE2D1F0F6D04FEC80363791807F ID:                       BNF:NORTHBOUND EMERGING MANAGER FUND B LP 325 N. SAINT      PAUL STREET SUITE 4900 DALLAS TX 75201 ID:004451210212      BNF BK: ID:                                                 INT BK: ID:                                                 RECV BK: ID:                                                PAYMENT DETAILS:
WIRE TYPE:BOOK OUT DATE:013018 TIME:1716 ET                 TRN:2018013000426338                                        FED IMAD:                                                   RELATED REF:9796E9BF1D3F7440                                ORIG:ECE2D1F0F6D04FEC80363791807FG ID:                       BNF:NORTHBOUND EMERGING MANAGER FUND B LP 325 N. SAINT      PAUL STREET SUITE 4900 DALLAS TX 75201 ID:004451210212      BNF BK: ID:                                                 INT BK: ID:                                                 RECV BK: ID:                                                PAYMENT DETAILS:

Art, CEO, AnalystFinder.com

 

art297
Opal | Level 21

Or, if your file is already a SAS dataset, then you could use:

data want;
  set have;
  informat orig $80.;
  orig=scan(substr(text,index(text,"ORIG:")+5),1);
  orig_length=length(orig);
run;

Art, CEO, AnalystFinder.com

 

 

 

PGStats
Opal | Level 21

If you prefer to use word functions

 

orig = scan(str, 1 + findw(str,"ORIG",": ","E"),": ");

 

PG
Reeza
Super User

You already have your data in a SAS data set?
If so, look into the SCAN function and/or FINDW to find the word ORIG. 

 

  x=scan(orig, -1, ':');

 


@HarryT wrote:

I have imported the file and kept only the Text column, which is made up of a text blob. One row is pasted below. I want to move the numbers after the word 'ORIG:' in bold below to there own data set so i can count how many characters they are. 

 

Any help will be appreciated.

 

 

WIRE TYPE:BOOK OUT DATE:013018 TIME:1716 ET                 TRN:2018013000426338                                        FED IMAD:                                                   RELATED REF:9796E9BF1D3F7440                                ORIG:ECE2D1F0F6D04FEC80363791807F ID:                       BNF:      BNF BK: ID:                                                 INT BK: ID:                                                 RECV BK: ID:                                                PAYMENT DETAILS:


 

kiranv_
Rhodochrosite | Level 12

i liked all the answers which are simpler and much cleaner, just one more way 

 

data want;
set have;
IF _N_ =1 THEN PATTERN = PRXPARSE ("/((?<=ORIG:)\S+)/");
retain pattern;
if prxmatch(pattern, orig) then 
 call prxposn(pattern, 1, position, length);
 val = substr(orig, position,length);
 drop pattern position;
 run;

 

 

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
  • 6 replies
  • 803 views
  • 4 likes
  • 6 in conversation