topic Re: Update dataset columns based on another dataset in SAS Programming

Update dataset columns based on another dataset

nickspencer — Thu, 12 Nov 2020 06:22:48 GMT

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?

Re: Update dataset columns based on another dataset

ChrisNZ — Thu, 12 Nov 2020 07:31:12 GMT

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

Re: Update dataset columns based on another dataset

nickspencer — Thu, 12 Nov 2020 13:40:23 GMT

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’

Re: Update dataset columns based on another dataset

Kurt_Bremser — Thu, 12 Nov 2020 14:02:53 GMT

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

Re: Update dataset columns based on another dataset

nickspencer — Thu, 12 Nov 2020 19:07:56 GMT

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

Re: Update dataset columns based on another dataset

ChrisNZ — Fri, 13 Nov 2020 04:00:37 GMT

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';