DATA Step, Macro, Functions and more

add one column from another table

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

add one column from another table

[ Edited ]

I have two table looks like and I want to add column score to tableA from tableB,then get tableC,how to do in SAS?

the only rule is to add a column in tableA name "score " and its value is same as column "score" in tableB(which are all the same in tableB)

+----+---+---+---+
| id | b | c | d |
+----+---+---+---+
|  1 | 5 | 7 | 2 |
|  2 | 6 | 8 | 3 |
|  3 | 7 | 8 | 1 |
|  4 | 5 | 7 | 2 |
|  5 | 6 | 8 | 3 |
|  6 | 7 | 8 | 1 |
+----+---+---+---+
       tableA

+---+---+-------+
| e | f | score |
+---+---+-------+
| 3 | 7 |    11 |
| 4 | 6 |    11 |
| 5 | 5 |    11 |
+---+---+-------+
      tableB

+----+---+---+---+-------+
| id | b | c | d | score |
+----+---+---+---+-------+
|  1 | 5 | 7 | 2 |    11 |
|  2 | 6 | 8 | 3 |    11 |
|  3 | 7 | 8 | 1 |    11 |
|  4 | 5 | 7 | 2 |    11 |
|  5 | 6 | 8 | 3 |    11 |
|  6 | 7 | 8 | 1 |    11 |
+----+---+---+---+-------+
       tableC

 


Accepted Solutions
Solution
‎03-07-2018 06:56 PM
Super User
Posts: 9,924

Re: add one column from another table

So there will always be only one distinct value in tableB for score?

 

In that case, create a macro variable from B and insert in A:

proc sql noprint;
select score into :score from tableb (obs=1);
quit;

data tablea_new;
set tablea;
score = &score;
run;

or do a simple merge with the first observation (to prevent unwanted observations if b contains more observations than a):

data tablea_new;
merge
  tablea
  tableb (keep=score obs=1)
;
run;

Both codes untested, for lack of usable example data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Valued Guide
Posts: 525

Re: add one column from another table

Please explain the rules to get the required observation from tableB.

Super User
Posts: 9,924

Re: add one column from another table

- please provide example data in data steps, so we can recreate your data with copy/paste and submit.

 

- what is the logical rule for combining A and B?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-07-2018 06:56 PM
Super User
Posts: 9,924

Re: add one column from another table

So there will always be only one distinct value in tableB for score?

 

In that case, create a macro variable from B and insert in A:

proc sql noprint;
select score into :score from tableb (obs=1);
quit;

data tablea_new;
set tablea;
score = &score;
run;

or do a simple merge with the first observation (to prevent unwanted observations if b contains more observations than a):

data tablea_new;
merge
  tablea
  tableb (keep=score obs=1)
;
run;

Both codes untested, for lack of usable example data.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 115 views
  • 0 likes
  • 3 in conversation