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:
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
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
If you prefer to use word functions
orig = scan(str, 1 + findw(str,"ORIG",": ","E"),": ");
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:
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.