topic Re: Looking up strings in one column and matching in another. in SAS Programming

Looking up strings in one column and matching in another.

hwangnyc — Mon, 10 Jun 2019 16:22:32 GMT

Hi everyone,

I have string data in two columns that are separated by commas. I'd like to scan the first column's characters and see if it appears in the second column.

Here is some sample data:

HaveA	HaveB
Homeless, Health Care, Aided	Health Care, Verified, Aided
A,B,C	A,C
X,Y,Z	J,K,F

This is what I want:

HaveA	HaveB	Want
Homeless, Health Care, Aided	Health Care, Verified, Aided	Health Care, Aided
A,B,C	A,C	A,C
X,Y,Z	J,K,F	.

Many thanks in advance!

Re: Looking up strings in one column and matching in another.

ballardw — Mon, 10 Jun 2019 16:30:19 GMT

Untested without data but has a chance of to get started.

data want;
   set have;
   length wantstr $100; /* or long enough to hold expected longest value*/
   do i= 1 to countw(HaveA,',');
      if index(haveb, scan(havea,i,','))>0 then wantstr=catx(',',wantstr,scan(havea,i,','));
   end;
   drop i;
run;

Countw and Scan functions are told to only consider comma as delimiter for finding phrases,