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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.