topic Re: How to Compare two tables column and get values of corresponding column value in SAS Programming

How to Compare two tables column and get values of corresponding column value

aniketwagare0 — Mon, 05 Dec 2016 16:07:07 GMT

I have two table A and B. I want compare column B1 from Table B with A1 from Table A. Whichever values will be matched want to add B2 column from Table B corresponding values in A3 column of table A.

Eg.

Table A,

Column :: SiteID, Name, Users

Table B

Column :: SiteID, Uniue User

Compare A.SiteID and B.SiteID. Whichever values will be matched want add B.Unique User corrensponding value to A.users

Thank You

Re: How to Compare two tables column and get values of corresponding column value

RW9 — Mon, 05 Dec 2016 16:13:19 GMT

Well, you've not posted any test data, so this code isn't tested:

proc sort data=tablea;
  by siteid;
run;
proc sort data=tableb;
  by siteid;
run;
data want;
  merge tablea tableb;
  by siteid;
run;

This will merge variables from second table to first based on matching siteid variable. For future, its a good idea to post test data (in the form of a datastep), and what the output should look like.

Re: How to Compare two tables column and get values of corresponding column value

Kurt_Bremser — Mon, 05 Dec 2016 16:15:04 GMT

First, sort both tables by siteid.

data want;
merge
  tablea (in=a)
  tableb (in=b)
;
by siteid;
if a;
if b then users = users !! unique_user;
if last.siteid then output;
drop unique_user;
run;

If you want the append to users to be done with a delimiter, use the catx() function.

Also make sure that users is long enough to hold all the values (add a length statement before the merge statement).

Re: How to Compare two tables column and get values of corresponding column value

Shmuel — Mon, 05 Dec 2016 16:20:38 GMT

Can tableB have siteIDs that are not in tableA ?

If you want to add columns from tableB only if siteID matches you nead

a slight change to @RW9 msrge step:

data want;
  merge tablea (in=ina)
        tableb;
  by siteid;
     if ina;
run;

Re: How to Compare two tables column and get values of corresponding column value

Shmuel — Mon, 05 Dec 2016 16:28:38 GMT

Can unique user be alraedy in tableA.users ?

If yes - would you like to add it again ?

Is tableA.users long enough to contain more users than original ?

If you meant to use @Kurt_Bremser code, you better make a slight change:

data want;
length users $100;  /* assign max length forecast */
merge
  tablea (in=a)
  tableb (in=b)
;
by siteid;
if a;
if b and index(users, unique_user) = 0 
   then users = compbl(users !! unique_user);
if last.siteid then output;
drop unique_user;
run;