topic Re: extract string from columns in SAS Programming

extract string from columns

Cho8 — Tue, 24 May 2022 16:42:23 GMT

Hi all,

need to extract 'ABCD' from the below sample data

text1	text2	text3	text4	text5
MARIANE BAFILE	ACF ASHLEY NOZIK NYS ABCD			MARIANE BAFILE
MARIANE BAFILE	ACF AMANDA NOZIK NYS ABCD			MARIANE BAFILE
MARTHA REITMAN ACF	LAURA REITMAN U/CA/ABCD #2			LAURA REITMAN
LORNA KENNEDY - PLOWRIGHT	ABCD KAVOY LINDSAY PLOWRIGHT
RANDALL CRAIG EX	CUSTODIAN FOR	SHERIDAN C EX	UNDER ABCD PMB321	RANDALL CRAIG EX
SANDRA J PUTMAN				SANDRA J PUTMAN
AARON GUTMAN				AARON GUTMAN

Re: extract string from columns

Tom — Tue, 24 May 2022 16:47:22 GMT

What does "extract" mean?
Are you asking to check for observations that have the string "ABCD" in any of this variables you show in your printout?

What about observations that have "ABCDE"? Should those be included?

data want;
  set have;
  where findw("ABCD",cats(' ',text1,text2,text3,text4,text5));
run;

Re: extract string from columns

Cho8 — Tue, 24 May 2022 16:50:06 GMT

NO, I need 'ABCD' , it may occur after / ,# or space.

Re: extract string from columns

Reeza — Tue, 24 May 2022 17:00:08 GMT

If the first table is your expected input data set, show the expected output data set you want after extraction.

Re: extract string from columns

Cho8 — Tue, 24 May 2022 17:06:22 GMT

expected output:

texta	textb	textc	textd	texte
	ABCD
	ABCD
	ABCD
	ABCD
			ABCD

Re: extract string from columns

Tom — Tue, 24 May 2022 17:27:36 GMT

You need to explain in more detail what you are trying to do. You cannot write code to extract a random word from a string (well you could but the result would be just a random word).

What is the rule that explains why the text ABCD should be pulled from those input strings. Why not any of the other words in the string. Does the ABCD change? Or are you literally looking for ABCD?

Re: extract string from columns

Reeza — Wed, 25 May 2022 14:54:46 GMT

data want;
set have;

array word_search(5) text1-text5;
array word_find(5) texta textb textc textd texte;

do i=1 to dim(word_search);
if findw(word_search(i), 'ABCD', 'it')>0 then word_find(i) = 'ABCD';
end;

run;

Suspect this will not generalize but based on what you've posted this would meet the requirements.

@Cho8 wrote:

Hi all,

need to extract 'ABCD' from the below sample data

text1 text2 text3 text4 text5

MARIANE BAFILE ACF ASHLEY NOZIK NYS ABCD MARIANE BAFILE

MARIANE BAFILE ACF AMANDA NOZIK NYS ABCD MARIANE BAFILE

MARTHA REITMAN ACF LAURA REITMAN U/CA/ABCD #2 LAURA REITMAN

LORNA KENNEDY - PLOWRIGHT ABCD KAVOY LINDSAY PLOWRIGHT

RANDALL CRAIG EX CUSTODIAN FOR SHERIDAN C EX UNDER ABCD PMB321 RANDALL CRAIG EX

SANDRA J PUTMAN SANDRA J PUTMAN

AARON GUTMAN AARON GUTMAN

Re: extract string from columns

Cho8 — Wed, 25 May 2022 06:19:06 GMT

its pattern,i need only that ,it may occur with delimiters like /,space,#.

it may occur in middle of the name with space delimiter..but idont need that string if its part of name..for eg.,

text1:forabcd.. idont require to extract..

textt1: forage abcd felix .. i have to extract abcd pattern

Re: extract string from columns

Reeza — Wed, 25 May 2022 14:55:43 GMT

If the posted solution does not work, show how with examples and it can be modified.

I modified the FIND to FINDW to search for words in the string.

Re: extract string from columns

Tom — Wed, 25 May 2022 15:31:39 GMT

@Cho8 wrote:

its pattern,i need only that ,it may occur with delimiters like /,space,#.

it may occur in middle of the name with space delimiter..but idont need that string if its part of name..for eg.,

text1:forabcd.. idont require to extract..

textt1: forage abcd felix .. i have to extract abcd pattern

Why are you using the word extract? What is it that you want to extract?

abcd is NOT a pattern. It is just a string of four letters. A pattern would be something like four letters. Then strings like ABCD or WXYZ would match that pattern.

If you just want to detect if the string ABCD appears in a variable you can use the FINDW() or INDEXW() function.

If you actually do have a pattern then perhaps you want to use regular expressions to search for them. But before you can write the regular expression you need say what pattern you are looking for.

Re: extract string from columns

Cho8 — Wed, 01 Jun 2022 11:43:52 GMT

data test_abcd;
set abcd;
test1=scan(acct_1,1," ""/","b");
test2=scan(acct_2,1," ""/","b");
test3=scan(acct_3,1," ""/","b");
test4=scan(acct_4,1," ""/","b");
test5=scan(acct_5,1," ""/","b");

run;

proc sql;
create table test2_abcd as select *,
(case when test1 like '%ABCD%' or test2 like '%ABCD%'
or test3 like '%ABCD%' or test4 like '%ABCD%'
or test5 like '%ABCD%' then 'Y' else 'N' end)as IND
from WORK.TEST_ABCD ;
quit;

Re: extract string from columns

Cho8 — Wed, 01 Jun 2022 11:45:29 GMT

in the above code, Iam not able to scan the whole string for the pattern, its only scanning last word.

Re: extract string from columns

Quentin — Wed, 01 Jun 2022 12:54:17 GMT

Did you try @Reeza's array solution? If so, what did you not like about that approach?

Re: extract string from columns

Cho8 — Wed, 01 Jun 2022 16:07:50 GMT

its throwing syntax error @findw.

Re: extract string from columns

Quentin — Wed, 01 Jun 2022 16:13:23 GMT

Please show the log with the code and the error message.

Re: extract string from columns

Cho8 — Thu, 02 Jun 2022 12:22:01 GMT

data utma_test2;
set test;

array search(5) acct_1-acct_5;
array find(5) test1 test2 test3 test4 test5;

do i=1 to dim(search);
if find(search(i), 'ABCD', 'i')>0 then find(i) = 'ABCD';
end;
run;

25 GOPTIONS ACCESSIBLE;
26 data utma_test2;
27 set test;
28
29 array search(5) acct_1-acct_5;
30 array find(5) test1 test2 test3 test4 test5;
NOTE: The array find has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as
array references and not function references.
31
32 do i=1 to dim(search);
33 if find(search(i), 'ABCD', 'i')>0 then find(i) = 'ABCD';
ERROR: Too many array subscripts specified for array find.
34 end;
35 run;

Re: extract string from columns

Quentin — Thu, 02 Jun 2022 14:24:17 GMT

The NOTE in the log explains the problem. Suggest changing the name of the array to Find_ or Found or whatever names makes sense and isn't already being used by SAS. You'll have to change the name in both the array statement and when you reference the array.