Hi Experts!
I need some help merging two tables.
Table A is a large dataset with 400+ columns and over 31,000 rows. Table B is smaller and shares some of the same columns (9) and rows (1600+) as Table A.
Both tables share a unique ID (unique_id) - I need to impute values from table B in to the corresponding missing field in table A by unique_id.
Here’s an example of what I want to do:
Table A
Unique_ID Var1 Var2 Var3 Var4 Var5 Var6……. Var400
MA_345 3 1 19 37.9 60 77
JM_909 . . . 40.2 55 67
TV_647 1 . . 37.7 62 83
ED_331 7 5 . 38.0 65 88
Table 2
Unique_ID Var1 Var2 Var3 Var4 Var5 Var6
JM_909 3 1 10 40.2 55 67
TV_647 1 0 15 37.7 62 83
Result
Unique_ID Var1 Var2 Var3 Var4 Var5 Var6……. Var400
MA_345 3 1 19 37.9 60 77
JM_909 3 1 10 40.2 55 67
TV_647 1 0 15 37.7 62 83
ED_331 7 5 . 38.0 65 88
Any help or guidance on this?
Thank you!
You might want to do an UPDATE. But you seem to want the reverse of the normal case it was designed to support. The UPDATE statement is designed to apply transactions to an existing dataset. Any missing value in the transaction dataset is ignored so the existing value is unchanged. So the non-missing values in the transaction dataset "win". You appear to want the reverse, where the non missing values in the original dataset "win". So just treat the transactions as the original dataset and original dataset as the transactions.
data want;
update table2 tableA;
by unique_id;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.