CODEWITHSUNDEEP
sqlsas
guudeer
guudeer

Reputation: 45

Update a table using another table in SAS

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.

Table t1

Key Var1 Var2

1    A    AA
2    B    BB
3    C    CC
4    D    DD
5    E    EE

Table t2

Key Var1 Var2
3    C    XX
4    D    YY
6    F    FF

Table t3

Key Var1 Var2
1    A    AA
2    B    BB
3    C    XX
4    D    YY
5    E    EE

The following SAS code would simply give me errors:

proc sql;
update t1 
set  var2 = t2.var2
from t1
inner join t2 on 
t1.key=t2.key;
quit;

Thanks!

Upvotes: 2

Views: 12185

Answers (2)

crow16384
crow16384

Reputation: 597

It could be done like:

data t1;
  input Key Var1 $ Var2 $;
  datalines;
1    A    AA
2    B    BB
3    C    CC
4    D    DD
5    E    EE
;

data t2;
  input Key Var1 $ Var2 $;
  datalines;
3    C    XX
4    D    YY
6    F    FF
;

title 'Table T1';
proc print data=t1 noobs;run;

title 'Table T2';
proc print data=t2 noobs;run;

Update itself:

proc sql;
  update t1 set var2 = (select var2 from t2 where t2.key=t1.key)
    where t1.key in(select key from t2);
quit;

Check:

title 'Updated T1';
proc print data=t1 noobs;run;

enter image description here enter image description here enter image description here

Upvotes: 4

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

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) );
    <ETL stuff>;
run;

data t2(index=(key) );
    <ETL stuff>;
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.

Upvotes: 1

Related Questions