topic Re: Comparing two columns and creating a new column based off comparison in SAS Programming

Comparing two columns and creating a new column based off comparison

Kbug — Thu, 17 Oct 2019 22:50:07 GMT

Hello, I am using SAS 9.4

and I am trying to compare two columns of data and make a new column that helps me identify if any data in column A is not in column B, (identified by 1=in column, 0=not in column)

Set: Have

A B

Bo Ape

Po Bo

Ro Cool

Fo Lo

No So

Mo Tape

So Yes

set: want

A B match (is data in A in B?)

Bo Ape 1

Po Bo 0

Ro Cool 0

Fo Lo 0

No So 0

Mo Tape 0

So Yes 1

So I am basing the data off of A and finding if any of A's data is found anywhere in B (refer to bolded).

How am I to go about this?

Re: Comparing two columns and creating a new column based off comparison

ballardw — Thu, 17 Oct 2019 23:19:53 GMT

Something like:

data work.have;
input A $ B $;
datalines;
Bo Ape 
Po Bo 
Ro Cool 
Fo Lo 
No So 
Mo Tape 
So Yes 
;

proc sql;
   create table work.match as
   select a, b,a in (select distinct b from work.have) as match
   from work.have
   ;
quit;

Re: Comparing two columns and creating a new column based off comparison

gamotte — Fri, 18 Oct 2019 12:45:39 GMT

Hello,

A data step solution :

proc sql noprint;
    SELECT nobs INTO :nobs TRIMMED
    FROM dictionary.tables
    WHERE libname="WORK" and memname="HAVE";
quit;

data want;
    array Bvals(&nobs.) $ _TEMPORARY_;
    if _N_=1 then do i=1 to &nobs.;
        set have point=i;
        Bvals(i)=B;
    end;

    set have;
    match=(A in Bvals);
run;