DATA Step, Macro, Functions and more

Reading words from one data set to another dataset

Reply
Occasional Contributor
Posts: 6

Reading words from one data set to another dataset

Im trying to read two variables from a dataset and loop them into another dataset until I get to the end of the file.

I am basically outputting to see if these words from the first data set are in any of the observations in the second dataset.

It runs, it just picks up everything.  Can anyone show me where my logic has gone wrong?

%global word1 word2;

data _null_;

set jessica.TwoWordsFeb;

call symput('word1', Word1);

call symput('word2', Word2);

CALL SYMPUT( 'TOTOBS' , _N_ );

run;

/*Runing prxmatch for word groupings*/

/*This looks at user defined particular words in the string*/

%MACRO LOOP;

%do i=1 %to &TOTOBS;

data temp;

set jessica.PercentImpFEBMar ;

if   prxmatch("/&word1./",search_term) gt 0

and  prxmatch("/&word2./",search_term) gt 0 then output;

run;

%end;

%mend;

%loop;

Contributor
Posts: 52

Re: Reading words from one data set to another dataset

This is slightly different, but it works. There is no need to use a macro to loop through a dataset. The DATA step does that automatically. I also don't know why your code limited the observations in dataset two to just the number in dataset one or why it read dataset one in twice.

data _null_;

   call symput('word1', "neiman");

   call symput('word2', "marcus");

run;

data temp;

   input search_term $25.;

   if prxmatch("/&word1./", search_term) gt 0 and prxmatch("/&word2./", search_term) gt 0;

   cards;

Neiman Marcus is here.

neiman marcus is there.

Neiman not Marcus

neiman Marcus

Neiman marcus

neiman marcus

;

run;

Occasional Contributor
Posts: 6

Re: Reading words from one data set to another dataset

Thanks Tish,

But this is slightly different because my words aren't restricted to just "Neiman" and "marcus".  I have 2 variables in dataset one (word1 and word2) that I need to loop through another data set to check for all words.

So for each word1, word2 combination, I need these words to loop through jessica.PercentImpFEBMar to see if each word1, word 2 pair is in any of the observations. 

Thanks!

PROC Star
Posts: 7,363

Re: Reading words from one data set to another dataset

I'm not sure if I correctly understand what you are trying to do but, hopefully, the following will at least provide you with an idea on how you might achieve what you are trying to accomplish:

data TwoWordsFeb;
  input words $20.;
  cards;
neiman
marcus
;

proc sql noprint;
  select words into :word1 separated by ' '
    from TwoWordsFeb;
  select words into :word1-:word&sqlobs.
    from TwoWordsFeb
  ;
quit;
%let numrecs=&sqlobs.;

data PercentImpFEBMar;
  informat search_term $50.;
  input search_term &;
  cards;
Neiman Marcus is here.
neiman marcus is there.
Neiman not Marcus
neiman Marcus
Neiman marcus
neiman marcus
;

%macro loop;
  data want (drop=x);
    set PercentImpFEBMar;
    x=1;
%do i=1 %to &numrecs.;
      x=x*prxmatch("/&&word&i./", search_term);
    %end;
    if x;
  run;
%mend;
%loop

Respected Advisor
Posts: 3,124

Re: Reading words from one data set to another dataset

Thanks, Art. This is my first contact with SQL automatic macro variable 'sqlobs', 'noprint' options here is a bit tricky, while it worked perfectly with the creation of macro variables. Nice to learn!

Haikuo

PROC Star
Posts: 7,363

Re: Reading words from one data set to another dataset

@Haikuo: glad you found it useful.  Just keep in mind: I'm a Psychologist NOT a programmer!  There are probably more efficient ways available to accomplish the same thing.

Art

Super User
Posts: 17,861

Re: Reading words from one data set to another dataset

This probably isn't more efficient depending on your data set but might be easier to understand.

proc sql;

     create table want as

     select  pifm.*

     from jessica.PercentImpFEBMar pifm

cross join jessica.twowordsfeb twf

where find(upcase(search_term), upcase(word1))>0 or find(upcase(search_term), upcase(word2))>0;

quit;

Super User
Posts: 9,682

Re: Reading words from one data set to another dataset

I am not sure whether I understand your question totally.

data word;
  input word1 : $20. word2 : $20.;
  cards;
ns ma
ma xd
;
run;
data have;
  input search_term $20.;
  cards;
nssdsma
asdansasa
smadshxdwe
sduso
;
run;

proc sql;
create table want as
 select distinct search_term
  from have,word
   where search_term contains strip(word1) and search_term contains strip(word2)
;
quit;



Ksharp

Ask a Question
Discussion stats
  • 7 replies
  • 346 views
  • 1 like
  • 6 in conversation