topic Appending a column from one table to another in SAS Programming

Appending a column from one table to another

pangea17 — Tue, 16 Apr 2019 22:49:52 GMT

I am using Enterprise Guide 7.13 and SAS version 9.4. I have two datasets. One has several field including a text field with sentences in it. The other dataset I created made up of key words. I want to search each of the fields in the first dataset to see if they contain one of the key words in the second dataset. If a row in the first dataset contains one of the key words, I want to append that word to the dataset for that row. If there is no match the new column would be empty. The end goal is to count the number of matches for each keyword. I have code that will do the matching and output those matches to a new dataset, but not append the key word as a separate variable.

I have an excel sheet enclosed that illustrates what I am looking for. The code below creates a dataset of the matches.

data _null_;

set dataset2 end=last;

if _n_ = 1 then call execute('data dataset3; set dataset1; ');

call execute(cats('if findw(upcase(information),"',upcase(word),'") > 0 then output;'));

if last then call execute('run;');

RUN;

Re: Appending a column from one table to another

PeterClemmensen — Tue, 16 Apr 2019 21:51:59 GMT

Can you post your datasets in a more usable form for us to work with?

Re: Appending a column from one table to another

ballardw — Tue, 16 Apr 2019 22:10:04 GMT

What does the output look like if two or more of the words in dataset2 are found in the same sentence?

Are the matches supposed to be case sensitive? i.e. does "Military" match "military"?

What are the rules for a word that appears in a composite use such as "police" in "policeman".

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Appending a column from one table to another

pangea17 — Tue, 16 Apr 2019 22:46:18 GMT

I believe SAS is case sensitive, so it is best to upcase everything so that you get a match. Just looking for an exact match. In your example policeman would not be a match for police. Trying to keep things simple for the time being. I will settle for the first work matching to be the only result.

Re: Appending a column from one table to another

Tom — Tue, 16 Apr 2019 23:09:29 GMT

Based on this code you posted :

data _null_;
  set dataset2 end=last;
  if _n_ = 1 then call execute('data dataset3; set dataset1; ');
  call execute(cats('if findw(upcase(information),"',upcase(word),'") > 0 then output;'));
  if last then call execute('run;');
RUN;

You are running a data step like:

data dataset3;
   set dataset1; 
   if findw(upcase(information),"NURSE") > 0 then output;
   if findw(upcase(information),"DOCTOR") > 0 then output;
   if findw(upcase(information),"POLICE") > 0 then output;
   ...
run;

Why not just join the two datasets directly?

proc sql;
create table dataset3 as
  select a.information,b.word
  from dataset1 a
  right join dataset2 b
  on findw(upcase(a.information),upcase(strip(b.word)))
;
quit;

Or reduce the time and space and just store the counts.

proc sql;
create table dataset3 as
  select b.word,count(*) as n_matches
  from dataset1 a
  right join dataset2 b
  on findw(upcase(a.information),upcase(strip(b.word)))
  group by 1
;
quit;

Re: Appending a column from one table to another

pangea17 — Wed, 17 Apr 2019 17:13:04 GMT

That was perfect Tom. Thanks!