BookmarkSubscribeRSS Feed
aniketwagare0
Fluorite | Level 6

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Shmuel
Garnet | Level 18

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;
Kurt_Bremser
Super User

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

Shmuel
Garnet | Level 18

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6576 views
  • 0 likes
  • 4 in conversation