find and move numbers into new data set after word ORIG:

Reply
New Contributor
Posts: 2

find and move numbers into new data set after word ORIG:

[ Edited ]

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:

PROC Star
Posts: 8,164

Re: find and move numbers into new data set after word ORIG:

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

 

Super Contributor
Super Contributor
Posts: 266

Re: find and move numbers into new data set after word ORIG:

Very nice, @art297
PROC Star
Posts: 8,164

Re: find and move numbers into new data set after word ORIG:

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

 

 

 

Esteemed Advisor
Posts: 5,532

Re: find and move numbers into new data set after word ORIG:

[ Edited ]

If you prefer to use word functions

 

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

 

PG
Highlighted
Super User
Posts: 23,747

Re: find and move numbers into new data set after word ORIG:

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:


 

PROC Star
Posts: 511

Re: find and move numbers into new data set after word ORIG:

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;

 

 

Ask a Question
Discussion stats
  • 6 replies
  • 308 views
  • 4 likes
  • 6 in conversation