BookmarkSubscribeRSS Feed
guillory21
Calcite | Level 5

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;

7 REPLIES 7
tish
Calcite | Level 5

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;

guillory21
Calcite | Level 5

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!

art297
Opal | Level 21

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

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

@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

Reeza
Super User

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;

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1029 views
  • 1 like
  • 6 in conversation