topic Re: how to extract key words from variables in New SAS User

how to extract key words from variables

changxuosu — Tue, 15 Jan 2019 14:54:21 GMT

Hi SAS community,

Thank you for looking at my post!

I have a question,

I have a variable which takes value like this:

1545253657356_EI_1_15_CUP_A

1545253657356_EI_1_15_MUG_A

1545265347356_EI_1_15_BOWL_A

1545T432657356_EI_1_15_CUP_B

15452657356_EI_1_15_MUG_B

1545265237356_EI_1_15_BOWL_B

I want to make my variable looks like this

CUP_A

MUG_A

BOWL_A

CUP_B

MUG_B

BOWL_B

that is, to delete the non-sense numbers and letters preceding the key words

Because I know the key words will take values only in this range:

(

CUP_A

MUG_A

BOWL_A

CUP_B

MUG_B

BOWL_B

)

and because the number of non-sense letter/numbers preceding the key words varies, so I don't want to use a substr function,

which I have to manually set the position of key words

anyone know any SAS function that achieve this using some logic like this

extract(variable, from list

(

CUP_A

MUG_A

BOWL_A

CUP_B

MUG_B

BOWL_B

))

Thanks in advance!!!

Re: how to extract key words from variables

Cynthia_sas — Tue, 15 Jan 2019 15:08:48 GMT

Hi:
There are functions, but you also have to use them with some kind of lookup technique. For your purposes, I'd probably use an array.
Cynthia

Re: how to extract key words from variables

novinosrin — Tue, 15 Jan 2019 15:31:32 GMT

data have;
input var $50.;
cards;
1545253657356_EI_1_15_CUP_A
1545253657356_EI_1_15_MUG_A
1545265347356_EI_1_15_BOWL_A
1545T432657356_EI_1_15_CUP_B
15452657356_EI_1_15_MUG_B
1545265237356_EI_1_15_BOWL_B
;

data want;
set have;
c=count(var,'_')-1;
i=0;
do pos = findc (var, '_') by 0 while (pos) ;            
i+1;
if i=c then leave; 
pos = findc (var, '_', pos + 1) ;                     
end ; 
want=substr(var,pos+1);
keep var want;
run;

@changxuosu Play with the above and see if this works. Countc or count is something I want to confirm along with find group of functions. Let me have a coffee and review again

Re: how to extract key words from variables

novinosrin — Tue, 15 Jan 2019 15:52:11 GMT

Using reverse, find and substr


data have;
input var $50.;
cards;
1545253657356_EI_1_15_CUP_A
1545253657356_EI_1_15_MUG_A
1545265347356_EI_1_15_BOWL_A
1545T432657356_EI_1_15_CUP_B
15452657356_EI_1_15_MUG_B
1545265237356_EI_1_15_BOWL_B
;

data want;
set have;
t=strip(reverse(var));
want=reverse(substr(t,1,findc(t,'_',3)-1));
drop t;
run;

Re: how to extract key words from variables

FreelanceReinh — Tue, 15 Jan 2019 15:59:20 GMT

Or use the SCAN function to extract the last two "words":

data want;
set have;
length want $6;
want=scan(var,-2,'_')||'_'||scan(var,-1,'_');
run;

Re: how to extract key words from variables

Cynthia_sas — Tue, 15 Jan 2019 16:14:01 GMT

Hi:
I have a couple of questions:
1) Is the string you want ALWAYS at the end of the variable?
2) What if the end of the variable had PLATE_A or CAT_A -- would you still want those to be extracted or do you only want the 6 strings you listed?
3) Are there ALWAYS underscores and ONLY underscores in the string?
4) You said the numbers at the beginning of the string were "nonsense", but are you sure you will NEVER need them?

Cynthia

Re: how to extract key words from variables

novinosrin — Tue, 15 Jan 2019 16:19:03 GMT

@FreelanceReinh Do you know the difference between genius and ordinary?. You may not, coz the former is synonym of who you are. That's what it is. Your presence of mind made that distinction. How I wish I could think like that. Simple and effective. Easy to handle and maintain. Kudos!

One day i will become good like you(hmm i hope).

PS Can't believe I didn't get the thought, the requirement is the concat of last two. Jeez! Will have to quit my evening pints and start eating veggies

Re: how to extract key words from variables

novinosrin — Tue, 15 Jan 2019 16:20:55 GMT

Hi @changxuosu, I think @Cynthia_sas questions are for you to answer although that seems to point to me. 🙂

Re: how to extract key words from variables

ballardw — Tue, 15 Jan 2019 16:37:47 GMT

And an approach that doesn't care where in the string the values may be:

data work.example;
   input var $50.;
   array t {6} $ 6 _temporary_ ("CUP_A","MUG_A","BOWL_A","CUP_B","MUG_B","BOWL_B");
   do i= 1 to dim(t);
      if index(var,t[i])>0 then do;
         var=t[i];
         leave;
      end;
   end;
   drop i;
cards;
1545253657356_EI_1_15_CUP_A
1545253657356_EI_1_15_MUG_A
1545265347356_EI_1_15_BOWL_A
1545T432657356_EI_1_15_CUP_B
15452657356_EI_1_15_MUG_B
1545265237356_EI_1_15_BOWL_B
1545265237356BOWL_B_EI_1_15_
1545265237356_EI_1_15_BOWL_B
1545265237356_EI_1_15_BOWL_B
;


run;

If the value in var may not all be upper case but you want the lower or mixed case to be changed as well use

      if index(upcase(var),t[i])>0 then do;

otherwise lower or mixed case such as bowl_a will remain in the long form.

Re: how to extract key words from variables

kiranv_ — Tue, 15 Jan 2019 16:38:52 GMT

one more way is to use regex

^(.+_)? this indicates to start from beginning and go till another instruction in this case till next pattern and this is first pattern

([^\_]+_[^\_]+)$ indicates anything_anything at the end and is second pattern

$2 indicates replace everything with second pattern

data want;
set have;
var_new = prxchange('s/^(.+_)?([^\_]+_[^\_]+)$/$2/', -1, trim(var));
run;

Re: how to extract key words from variables

Cynthia_sas — Tue, 15 Jan 2019 17:32:19 GMT

Yes, those questions were for the original poster. Sorry, I just clicked so it would be at the end of the thread, not in the middle. It seems to me that there are points that really need to be clarified before writing a program. For example, what if CUP_A can be in the middle of the string? Then the REVERSE technique won't work. What if the value in the string is cup_a instead of CUP_A? What if there aren't any underscores in the string? Just a bunch of unknowns that need to be answered before I would attempt a solution. But of all the solutions, posted here, the one that I was thinking of is similar to the ARRAY solution because that allows you to provide a specific list of desired strings to search for with the INDEX function.
Cynthia

Re: how to extract key words from variables

Ksharp — Wed, 16 Jan 2019 09:15:24 GMT

It is depended on your criteria of Keyword.

data have;
input var $50.;
cards;
1545253657356_EI_1_15_CUP_A
1545253657356_EI_1_15_MUG_A
1545265347356_EI_1_15_BOWL_A
1545T432657356_EI_1_15_CUP_B
15452657356_EI_1_15_MUG_B
1545265237356_EI_1_15_BOWL_B
;

data want;
set have;
key=substr(var,findc(var,,'db')+2);
run;

Re: how to extract key words from variables

changxuosu — Thu, 17 Jan 2019 19:44:14 GMT

it works perfect. thank you so much Ksharp..
What does the modifier 'db' mean? findc(var,,'db')+2 returns the position of the first letter of the last two words (my "key word"). How this is achieved in such a simple function?! it's like magic!

Re: how to extract key words from variables

changxuosu — Thu, 17 Jan 2019 19:45:57 GMT

wow, this prxchange works like magic, it's my first time seeting it. will learn it. thanks a lot!

Re: how to extract key words from variables

changxuosu — Thu, 17 Jan 2019 19:46:28 GMT

it's a smart loop! thank you so much! it worked like magic!

Re: how to extract key words from variables

changxuosu — Thu, 17 Jan 2019 19:46:57 GMT

these are great questions, thank you Cynthia!

Re: how to extract key words from variables

changxuosu — Thu, 17 Jan 2019 19:47:43 GMT

i studied your code and it worked fantastic! thank you novinosrin. hope you enjoyed your coffee 🙂

Re: how to extract key words from variables

Ksharp — Fri, 18 Jan 2019 05:50:47 GMT

modifier 'db' means :

'd' only search 0-9 number,

'b' search number from RIGHT .

Re: how to extract key words from variables

changxuosu — Fri, 18 Jan 2019 14:12:34 GMT

wow! that's pretty smart! Learned a lot! Thanks a lot!

Re: how to extract key words from variables

changxuosu — Thu, 28 Feb 2019 21:06:27 GMT

thank you so much for your input. it opened my door to the new world of regex, never knew it before!