topic Re: add one column from another table in SAS Programming

add one column from another table

Geo- — Tue, 06 Mar 2018 08:17:18 GMT

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

Re: add one column from another table

andreas_lds — Tue, 06 Mar 2018 07:45:53 GMT

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

Re: add one column from another table

Kurt_Bremser — Tue, 06 Mar 2018 07:56:02 GMT

- 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?

Re: add one column from another table

Kurt_Bremser — Tue, 06 Mar 2018 08:28:46 GMT

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.

Re: add one column from another table

Raymo70 — Fri, 22 Mar 2019 15:15:51 GMT

KurtBremser

This code works very well for numeric and i am very appreciative of that, What would the code look like if the data were character?

Thanks!

Re: add one column from another table

Kurt_Bremser — Mon, 25 Mar 2019 06:43:39 GMT

Then you need to enclose the text created by the macro variable in quotes:

score = "&score.";