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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: