Subject: update_a_table_using_another_table_in_sas


Question
-----------

   I have table t1 and t2, how can I create table t3? Basically I want to update Var2 in t1 where t1.Key=t2.Key, 
   using the data from t2 while keeping everything else in t1 the same.

Answer
--------------

   You can use an update statement in a data step. 
   Update statements are very similar to merge statements, except that they do not replace populated values with missing values 
   unless you specify. In addition, non-unique keys generate a warning, which is helpful for debugging.

   The general syntax for updating tables and creating a new one simultaneously:

  Syntax

	data <NewTable>;
    		update <MasterTable>
           	       <TransactionTable>;
    		by <key(s)>;
	run;

  In order to do an update operation with your data, you need to make sure that your two datasets are either sorted or 
  indexed by the key variable. If it's not sorted or indexed, SAS will complain and the update will fail. In general, 
  I just add the index option at the final steps that I need to do to create the master or transactional datasets. 
  It tends to save some time because an additional sorting step is prevented.

  Step 1: Make sure Tables 1 & 2 are either indexed or sorted by key
         -------------------------------------------------------------------

	data t1(index=(key) );
    		;
	run;

	data t2(index=(key) );
    		;
	run;

  Step 2: Create a new table, t3, by updating t1 with matching values from t2
          -------------------------------------------------------------------

	data t3(index=(key) );
   	    update t1(in=t1_key)  /* Same thing as SQL t1.key */
          	   t2(in=t2_key); /* Same thing as SQL t2.key */
   	    by key;

   		/* This is SAS's way of specifying a left-join */
   		if(t1_key);
	run;

	Though you do not specifically need the index= option on t3 here, it is good practice to keep your key variable indexed 
        or have an integrity constraint applied to it.

------------------------------------------------------------------------------------------------
Credit to:
 Stu Sztukowski
 https://stackoverflow.com/questions/35656088/