BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
alex_philby
Obsidian | Level 7

Hi everyone,

 

I have two tables. Table 1 looks like this :

 

cat
dog
lion
hamster
bear
guinea pig

 

And table 2 looks like this :

 

my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute

 

What I'd like as a result is to have a count of table 1 words occurences in table 2. To make myself clear, something like this :

 

words of

table 1

occurrences in table 2
cat2
dog2
lion3
hamster1
bear1
guinea pig0

 

You will notice that plural forms of words, like "lions" for instance, are counted. In fact, we're looking for the chain of character "lion" in table 2, not the exact word.

 

I've been trying to split the strings in table 2 into words with the scan function, and running some proc freq and merges, but I'm not going anywhere.

 

Don't hesitate to ask any question. English is not my mother language so maybe I'm not being very clear.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

An SQL approach

 

data have1;
input animals$50.;
datalines;
cat
dog
lion
hamster
bear
guinea pig
;

data have2;
input sentence$200.;
infile datalines truncover;
datalines;
my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute
;

proc sql;
	create table want as
	select animals
		  ,sum(ifn(find(sentence, strip(animals))>0, 1, 0)) as occurences
	from have1, have2
	group by animals
	order by calculated occurences desc;
quit;

 

View solution in original post

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

What if the word "dog" appears twice in the same line?

alex_philby
Obsidian | Level 7
Sorry, it would count as a single occurence 🙂
PeterClemmensen
Tourmaline | Level 20

An SQL approach

 

data have1;
input animals$50.;
datalines;
cat
dog
lion
hamster
bear
guinea pig
;

data have2;
input sentence$200.;
infile datalines truncover;
datalines;
my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute
;

proc sql;
	create table want as
	select animals
		  ,sum(ifn(find(sentence, strip(animals))>0, 1, 0)) as occurences
	from have1, have2
	group by animals
	order by calculated occurences desc;
quit;

 

alex_philby
Obsidian | Level 7

Thanks a lot. It works with the small data I've given as an example. Sadly, when I use it on the tables I'm working with (table 1 contains 1.7 millions observations and table 2, 7k observations), I get a "sort execution failure" error. I'm now trying to deal with this...I've found it could be a memory issue.

andreas_lds
Jade | Level 19

Using a hash-object and an iterator:

 

data have1;
input animal $50.;

occurences = 0;

datalines;
cat
dog
lion
hamster
bear
guinea pig
;
run;

data have2;
input sentence $200.;
infile datalines truncover;

datalines;
my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute
;
run;

data _null_;
   if 0 then set work.have1;

   set work.have2 end= jobDone;

   if _n_ = 1 then do;
      declare hash h(dataset: 'work.have1', multidata: 'yes');
      declare hiter iter('h');
      h.defineKey('animal');
      h.defineData('animal', 'occurences');
      h.defineDone();
   end;

   rc = iter.first();

   do while (rc = 0);
      if find(sentence, animal, 'it') then do;
         occurences = occurences + 1;
         h.replace();         
      end;

      rc = iter.next();
   end;

   if jobdone then do;
      h.output(dataset: 'work.want');
   end;
run;

proc sort data=work.want;
   by descending occurences;
run;

Without the not-exact-match requirement something like the 5th example in http://support.sas.com/documentation/cdl/en/lecompobjref/69740/HTML/default/viewer.htm#p00ilfw5pzcjv... could be created - faster and less code.

alex_philby
Obsidian | Level 7
It works with small data tables, but my SAS session still crashes when I try to run it on the 1mil+ observations tables.
mkeintz
PROC Star

This program does not attempt a Cartesian join in memory, so it may be more efficient:

 

Editted modification.  I overlooked the fact that you wanted frequencies in descending order, with just 2 columns.  Here's a revised program using the same approach:

 

data have1;
input animals $50.;
datalines;
cat
dog
lion
hamster
bear
guinea pig
;

data have2;
input sentence $200.;
infile datalines truncover;
datalines;
my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute
;
proc sql noprint;
  select max(length(trim(animals))) into :maxlen from have1;
  select quote(trim(animals))       into :val_list separated by "," from have1;
quit;
%put _user_;

data want (keep=animals freq);
  array n_   {&sqlobs} ;
  array vals {&sqlobs} $&maxlen (&val_list);

  set have2 end=eoh;
  do w=1 to &sqlobs;
    n_{w} + (find(sentence, strip(vals{w}))>0);
  end;

  if eoh;
  do i=1 to &sqlobs;
    ix=whichn(max(of n_{*}),of n_{*});
    animals=vals{ix};
    freq=n_{ix};
    output;
    n_{ix}=.;
  end; 
run;

 

 

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
alex_philby
Obsidian | Level 7
This one too is working with small data tables, but makes my SAS session crash when I try to run it on the 1mil+ observations tables. Sorry everyone, I guess it's just a memory problem. I can run simple data step on the mentionned tables, but your programs makes my session crash, for some reason.
Ksharp
Super User

I don't understand

"In fact, we're looking for the chain of character "lion" in table 2, not the exact word."

 

 

data have1;
input animals$50.;
datalines;
cat
dog
lions
hamster
bear
guinea pig
;

data have2;
input sentence$200.;
infile datalines truncover;
datalines;
my dog is stupid
that cat likes my dog
fat hamster
bear are dangerous
lions are dangerous
lions eat antelopes
I like cats and lions
giraffes are cute
;

data _null_;
 if _n_=1 then do;
   if 0 then set have1;
   declare hash h(dataset:'have1');
   declare hiter hi('h');
   h.definekey('animals');
   h.definedone();

   declare hash hh();
   hh.definekey('animals');
   hh.definedata('animals','n');
   hh.definedone();
 end;
set have2 end=last;
do while(hi.next()=0);
 if find(sentence,strip(animals)) then do;
  if hh.find()=0 then do;n=n+1;hh.replace(); end;
   else do;n=1;hh.add();end;
 end;
end;

if last then hh.output(dataset:'want');
run;

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
  • 9 replies
  • 1067 views
  • 6 likes
  • 5 in conversation