topic find the shortest distance between terms within text in SAS Programming

find the shortest distance between terms within text

gyambqt — Tue, 18 Jul 2017 01:27:31 GMT

Hello Experts,

For unknown reason, my OP was deleted.

I will post my question here again.

if I have a table below:

ID text term

1 ABCAB A

1 ABCAB B

I want to find the shortest distance between terms within same ID.

P.S same ID would have same text

For example: in the first record I have ID=1 text=ABC term=A , in the second record I have ID=1 text=ABC and term=B

I want to compare A with A, and A with B, and B with B for ID=1

the position of first A is 1, the second A has a position of 4 so the distance between A with A is 4-1=3

the position of first A is 1, the first B has a position=2, the second B has a position=5

so the distance between first A with first B is 2-1=1 and the distance between first A with second B is 5-1=4 so shortest distance between A with B is 1

the position of second A is 4, the position of first B is 2 so the distance is 2-4=-2,

the position of second A is 4, the position of second B is 5 so the distance is 5-4=1

etc.

so the final table should look like:

ID term1 term2 shortest distance

1 A A 1

1 A B 1

1 B B 3

Find the shortest distance between terms within text

gyambqt — Mon, 17 Jul 2017 06:27:12 GMT

Hello Experts,

For unknown reason, my OP was deleted.

I will post my question here again.

if I have a table below:

ID text term

1 ABCAB A

1 ABCAB B

I want to find the shortest distance between terms within same ID.

P.S same ID would have same text

For example: in the first record I have ID=1 text=ABC term=A , in the second record I have ID=1 text=ABC and term=B

I want to compare A with A, and A with B, and B with B for ID=1

the position of first A is 1, the second A has a position of 4 so the distance between A with A is 4-1=3

the position of first A is 1, the first B has a position=2, the second B has a position=5

so the distance between first A with first B is 2-1=1 and the distance between first A with second B is 5-1=4 so shortest distance between A with B is 1

the position of second A is 4, the position of first B is 2 so the distance is 2-4=-2,

the position of second A is 4, the position of second B is 5 so the distance is 5-4=1

etc.

so the final table should look like:

ID term1 term2 shortest distance

1 A A 1

1 A B 1

1 B B 1

Thanks

Re: Find the shortest distance between terms within text

Kurt_Bremser — Mon, 17 Jul 2017 06:30:14 GMT

Your original post is still there: https://communities.sas.com/t5/General-SAS-Programming/find-the-shortest-distance-between-terms-within-text/m-p/376399

Re: Find the shortest distance between terms within text

gyambqt — Mon, 17 Jul 2017 06:33:36 GMT

Ah...I received a message saying that post was deleted...

Re: find the shortest distance between terms within text

RW9 — Mon, 17 Jul 2017 08:19:46 GMT

Right, well your post is quite confusing, and do remember to post test data in the form of a datastep in future. This shold start you off:

data have;
  input id text $ term $;
datalines;
1 ABCAB A
1 ABCAB B
;
run;

data want;
  set have;
  do i=1 to lengthn(text);
    dist=999;
    do j=i to lengthn(text);
      if char(text,i)=char(text,j) and j-i < dist then dist=j-1;
    end;
  end;
run;

So you see you can loop over a string, which is just an array of characters.

Re: find the shortest distance between terms within text

gyambqt — Mon, 17 Jul 2017 23:41:58 GMT

sorry for the confusion, I have modified the question in accordance to my other post (same question). I hope this helps to clarify.

Re: find the shortest distance between terms within text

ChrisNZ — Tue, 18 Jul 2017 01:24:38 GMT

How is the B-B distance 1?

Re: find the shortest distance between terms within text

gyambqt — Tue, 18 Jul 2017 01:28:34 GMT

sorry it was typo.....it is fixed now!

Re: find the shortest distance between terms within text

ChrisNZ — Tue, 18 Jul 2017 01:58:29 GMT

Like this?

data HAVE;
input ID TEXT $ TERM $;
cards;
1 ABCAB A
1 ABCAB B
run;
data WANT;
  retain TERMS;
  length TERMS $80;
  set HAVE;
  by ID;
  if first.ID then TERMS='';
  if not findw(TERMS,trim(TERM)) then TERMS=catx(' ', TERMS, TERM); 
  if last.ID then do I=1 to countw(TERMS,' ');
    do J=1 to countw(TERMS,' ');
      TERM1 =scan(TERMS, I);
      TERM2 =scan(TERMS, J);
      DIST  =1e9;
      POS1  =0 ;
      do until(POS1=0|POS2=0);
        POS1=find(TEXT,trim(TERM1),POS1+1);  
        POS2=find(TEXT,trim(TERM2),POS1+1);  
        if POS1 & POS2 then DIST=min(DIST,POS2-POS1);
      end;
      output;
    end; 
  end; 
run;  
proc print noobs;
  var ID TERM1 TERM2 DIST;
run;

ID	TERM1	TERM2	DIST
1	A	A	3
1	A	B	1
1	B	A	2
1	B	B	3

Re: find the shortest distance between terms within text

gyambqt — Tue, 18 Jul 2017 03:20:03 GMT

Hi thanks for your prompt reply.

the shorted distance between A and B or B and A should be same both =1

Re: find the shortest distance between terms within text

ChrisNZ — Tue, 18 Jul 2017 03:51:06 GMT

I think I got you close enough.

Just do a quick post-processing step if needed.

Re: find the shortest distance between terms within text

gyambqt — Tue, 18 Jul 2017 05:39:05 GMT

HI I realise there is a small issue with the code and I modified below:

data want;

retain terms;

length terms $32767;

set troy;

by source_key_value1;

if first.source_key_value1 then

terms='';

if not findw(terms,trim(upcase(term))) then

terms=catx(' ', upcase(terms), upcase(term));

if last.source_key_value1 then

do i=1 to countw(upcase(upcase(terms)),' ');

do j=1 to countw(upcase(terms),' ');

term1 =scan(upcase(terms), i);

term2 =scan(upcase(terms), j);

dist =999999;

pos1 =0;

pos2 =0;

do until(pos1=0 or pos2=0);

pos1=find(compress(msg1),trim(upcase(term1)),pos1+1);

if upcase(term1)=upcase(term2) then

pos2=pos1;

pos2=find(compress(msg1),trim(upcase(term2)),pos2+1);

if pos1 & pos2 then

dist=min(dist,abs(pos2-pos1));

end;

output;

end;

run;

Re: find the shortest distance between terms within text

ChrisNZ — Tue, 18 Jul 2017 21:21:11 GMT

There are no issues with the code and it did axactly what you requested.

If you have length and case to take into account, use the find() function's modifiers: find(V1,V2,'it') -rather than upcase() and compress() and trim()- to make more legible code.

Re: find the shortest distance between terms within text

Peter_C — Thu, 20 Jul 2017 08:41:29 GMT

Will there ever be more than two terms for an ID?
Is there a text length limit? Is it 5 characters or more?