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
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.
Please explain the rules to get the required observation from tableB.
- 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?
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.
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!
Then you need to enclose the text created by the macro variable in quotes:
score = "&score.";
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.