DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 13

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

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

Super User
Super User
Posts: 7,401

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

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.

Trusted Advisor
Posts: 1,378

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

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;
Super User
Posts: 6,938

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

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).

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,378

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

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 @KurtBremser 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;
Ask a Question
Discussion stats
  • 4 replies
  • 529 views
  • 0 likes
  • 4 in conversation