Populate column with the values from another table

[Wickedestjr]

Hello,

I have Table1 with a "Name" column and an "ID" column. All values in the "Name" column are unique from one another. However, the "ID" column is completely blank; there are no values populated.

I also have Table2 that has the same "Name" and "ID" columns. It has all the same "Name" values from Table1, but the names are in a different order. The "ID" values are all populated in this table. 

What is the easiest way for me to populate the "ID" column in Table1 using the "ID" values from Table2? The "Name" values are ordered completely differently in both tables, but I need to make sure to match up the correct "ID" values for each name. I need to keep everything about Table1 the same - particularly the way that the rows and columns are ordered.

Any help would be greatly appreciated!

[ballardw]

Since already have an ID "column" (really should use Variable in SAS terms) what properties does it have? What type of variable numeric or character? What is its length? (If you don't know the answers run Proc Contents data=yourdatsetname; run; the results will show the properties). This is important because if the variables are not of the same type then conversion needs to be done. AND if the lengths are not compatible then modifications need to be done to make and ID column that will accept the new values and that is not easy with your requirement for column order.

Note: For 99.99 percent of use the order of variables in a SAS data set is not important. It may be convenient for a human for some purposes but does not affect any program results.

Order of observations (rows), yes that can matter. Which brings up a simple question like Is your data sorted by the Name variable? If so it is easy to recover the row order as needed. If not you may need to add a variable to hold the current order.

Since you don't provide an actual description of your current variable (column) order or the names of the other variables for the complete data set we can't provide anything that looks like working code.

The two most common approaches Proc SQL join on a the name values does't guarantee maintaining any order.

The other a Data step Merge requires both sets to be sorted by the Name before starting.

[Tom · Accepted solution]

Let's express this problem in a different way.

You have one dataset, let's call it TABLE1, with a variable called NAME.  Whether or not it already has a variable named ID does not matter since it is empty.  

You have a second dataset, let's call it TABLE2, which also have NAME but this one actually has values for the variable ID.

And you want a new dataset that has the information in TABLE1 with the addition of the values of ID from TABLE2.  That sounds like a simple MERGE operation.

So first makes sure both TABLE1 and TABLE2 are sorted by NAME and then just MERGE them.   If TABLE2 has other variables you don't care about you can use KEEP= dataset option to ignore them.

data want;
  merge table1(in=in1) table2(keep=name id);
  by name;
  if in1;
run;