Update dataset columns based on another dataset

[nickspencer]

Hello all,
I have a master dataset with few hundred columns including columns ID, x ,y and status. I have another dataset table2 that also has columns ID and status2.
I want to update the column values of x and y on master dataset based on criteria that involves both master and table2 datasets. I am doing this in teradata sql right now.

Update a
From master a, table2 b
set x=1,y=1
where a.status=‘Inactive’
and b.status2=‘Active’

Can I do the same update with sas datasets?

[ChrisNZ]

I don't see any join criteria here.How is table b used?

[nickspencer]

Chrisnz I missed the join condition on the query. Here is the complete query.
Update a
From master a, table2 b
set x=1,y=1
where a.ID=b.ID
and a.status=‘Inactive’
and b.status2=‘Active’

[Kurt_Bremser]

You can do it with SAS datasets, but if the end result has to be a Teradata table, you best do the update there.

[nickspencer]

@Kurt_Bremser
I does not have to be a teradata table. I need the output as a sas dataset.

[ChrisNZ · Accepted solution]

Standard ANSI SQL does this. For example:

data MASTER; A=0; X=0; Y=0; STATUS='Inactive' ;
data TABLE2; A=0; STATUS2='Active';
run;
proc sql;
  update MASTER
  set X=1,Y=1
  where A in (select unique A from TABLE2 where STATUS2='Active')
    and STATUS='Inactive';