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
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.
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;
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).
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.