BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Geo-
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

5 REPLIES 5
andreas_lds
Jade | Level 19

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

Kurt_Bremser
Super User

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

Kurt_Bremser
Super User

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.

Raymo70
Calcite | Level 5

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!

 

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
  • 5 replies
  • 20237 views
  • 1 like
  • 4 in conversation