topic Re: Reading words from one data set to another dataset in SAS Programming

Reading words from one data set to another dataset

guillory21 — Thu, 26 Jul 2012 16:26:51 GMT

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;

Re: Reading words from one data set to another dataset

tish — Thu, 26 Jul 2012 16:57:07 GMT

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;

Re: Reading words from one data set to another dataset

guillory21 — Thu, 26 Jul 2012 18:05:41 GMT

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!

Re: Reading words from one data set to another dataset

art297 — Thu, 26 Jul 2012 18:22:24 GMT

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

Re: Reading words from one data set to another dataset

Haikuo — Thu, 26 Jul 2012 21:46:46 GMT

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

Re: Reading words from one data set to another dataset

art297 — Thu, 26 Jul 2012 22:20:20 GMT

@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

Re: Reading words from one data set to another dataset

Reeza — Thu, 26 Jul 2012 22:27:59 GMT

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;

Re: Reading words from one data set to another dataset

Ksharp — Fri, 27 Jul 2012 04:21:59 GMT

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