topic find and move numbers into new data set after word ORIG: in SAS Programming

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

HarryT — Fri, 09 Feb 2018 20:34:06 GMT

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:

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

art297 — Fri, 09 Feb 2018 20:29:46 GMT

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

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

HB — Fri, 09 Feb 2018 20:37:11 GMT

Very nice, @art297

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

art297 — Fri, 09 Feb 2018 20:39:05 GMT

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

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

Reeza — Fri, 09 Feb 2018 20:41:22 GMT

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:

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

PGStats — Sat, 10 Feb 2018 05:51:15 GMT

If you prefer to use word functions

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

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

kiranv_ — Sat, 10 Feb 2018 15:59:04 GMT

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;