topic Re: search words in table --- index in ODS and Base Reporting

search words in table --- index

deleted_user — Thu, 23 Nov 2006 09:52:15 GMT

Hello,

My goal is to initialize a field to :
0 if the record in the agtxti_copy.sas7bdat table does not contain the words of the probable.sas7bdat table.
1 if the record contains the word present in the probable.sas7bdat table.

The problem is that the field is always equals to 0 even if the field in the agtxti_copy.sas7bdat table contains the word of the probable.sas7bdat table...

Here is my source code :

----------------------------
%MACRO L(M=);
(index(txtaga||txtagb,"&M")+index(txtagc||txtagd,"&M")+index(txtage||txtagf,"&M"))
%MEND L;

data result.agtxti_critpi /DEBUG;
set agtxti_copy;
dsid=open("siqf.probable");
nobs=attrn(dsid,"nobs");
do i=1 to nobs;
rc = fetchobs(dsid,1);
mot1=getvarc(dsid,varnum(dsid,'MOT1'));
ARR_M1 =%L(M=eval(mot1)); /* this does not work because ARR_M1 = 0*/
ARR_M2 =%L(M=IMMOBILIS); /* this works because ARR_M2 > 0*/
test1=index(txtagc,mot1); /* this does not work because test1 = 0*/
test2=index(txtagc,'IMMOBILIS'); /* this works because test2 > 0*/
put test;
end;
dsid=close(dsid);
run;

-----------------------------
Can somebody help me ?

Thank you for your answer.

Marion

Re: search words in table --- index

Olivier — Thu, 23 Nov 2006 14:38:13 GMT

Bonjour Marion.

I don't know if your data looks like this example, but maybe trying something with the INDEXW function and merging the two datasets with the SQL procedure can help.

DATA work.probable ;
INPUT word :$20. ;
CARDS ;
pain
headache
sore
;
RUN ;
DATA work.agtxti_copy ;
INFILE CARDS DLM = "/" ;
INPUT txtaga :$200. ;
CARDS ;
No problems at all.
Feeling of sore throat.
Feeling some pain in the neck.
Sore throat.
;
RUN ;
PROC SQL ;
SELECT txtaga AS sentence,
MAX((INDEXW(UPCASE(a.txtaga), UPCASE(b.word))>0)) AS word_is_in_sentence
FROM work.agtxti_copy AS a,
work.probable AS b
GROUP BY sentence
;
QUIT ;

Re: search words in table --- index

deleted_user — Fri, 24 Nov 2006 08:46:20 GMT

Thank you for your answer but I have again a problem...

In fact, we can have parts of words to search and probably not a entire word...

So I keep your example and I used the index function instead of the indexw function. But it doesn't work. Here is the result I obtained :
sentence : ENTRAINE PANNE IMMOBILISANTE
part of word : IMMOBILIS
index : 0

and here is my source code (the same as yours) :
PROC SQL /DEBUG;
SELECT a.txtagc AS sentence,
b.mot1 AS word,
INDEX(a.txtagc,b.mot1) AS index,
MAX((INDEX(UPCASE(a.txtagc), UPCASE(b.mot1))>0)) AS word_is_in_sentence
FROM work.agtxti_copypeu AS a,
siqf.probablepeu AS b
GROUP BY sentence
;
QUIT ;

is the proc sql more powerful than the code I mentionned (open the table, ...) ?

Thank you for your answer.

Re: search words in table --- index

deleted_user — Fri, 24 Nov 2006 08:56:45 GMT

Just one more thing. My code in my first post could not works because I forgot something :

%MACRO L(M=);
(index(txtaga||txtagb,"&M")+index(txtagc||txtagd,"&M")+index(txtage||txtagf,"&M"))
%MEND L;
data result.agtxti_critpi ;
set agtxti_temp;
dsid=open("siqf.probable");
nobs=attrn(dsid,"nobs");
ARR_M_TEMP=0;
ARR_M=0;
do i=1 to nobs;
rc = fetchobs(dsid,i);
mot1=getvarc(dsid,varnum(dsid,'MOT1'));
/* Rajout */
%let mot1=&mot1;
ARR_M_TEMP = ARR_M_TEMP + %L(M=&mot1);
test=index(txtaga||txtagb,"&mot1")+index(txtagc||txtagd,"&mot1")+index(txtage||txtagf,"&mot1");
end;
if ARR_M_TEMP>0 then do;
ARR_M=1;
else do;
ARR_M=0;
end;
dsid=close(dsid);
run;

Re: search words in table --- index

Olivier — Fri, 24 Nov 2006 14:01:14 GMT

Well Marion, I think that the main problem your program encounters is mixing macro-language and SAS language : both can work together, but not at the same time.
When you submit a program including both macro and normal statements, the macro facility is compiled and executed first ; it should return text that is considered as SAS code. And then the transform program is executed.
For example, when you submit :

DATA _NULL_ ;
SET sashelp.class (OBS = 1) ;
%LET MVage = age ;
RUN ;
PROC PRINT DATA = sashelp.class (WHERE = (age > &MVage)) ;
RUN ;

The first executed statement is
%LET MVage = age ;

it does not mean that the VALUE of the SAS variable AGE is copied to the macro-variable MVage, but that the TEXT "AGE" is now the value of the macro-variable.
Then the Data Step is processed :

DATA _NULL_ ;
SET sashelp.class ;
RUN ;

Then the &MVage in the WHERE clause is transformed :
&MVage --> AGE

So at the end, the executed PRINT procedure is :
PROC PRINT DATA = sashelp.class (WHERE = (age > age)) ;
RUN ;

As the condition is always false (a value can't be > than itself !), nothing is printed.

I think that's why your way of doing should never come to a correct result.

But why using macro-language in that case ? Just skip the macro-things and try just this :

data result.agtxti_critpi /DEBUG;
set agtxti_copy;
dsid=open("work.probable");
nobs=attrn(dsid,"nobs");
do i=1 to nobs;
rc = fetchobs(dsid,1);
mot1=getvarc(dsid,varnum(dsid,'MOT1'));
ARR_M1 = (index(txtaga||txtagb,mot1)+index(txtagc||txtagd,mot1)+index(txtage||txtagf,mot1));
end;
dsid=close(dsid);
run;