topic Re: Using coalesce to update data in one column form one table with data in one column form another in SAS Programming

Using coalesce to update data in one column form one table with data in one column form another tabl

2222 — Mon, 27 Jun 2022 04:12:23 GMT

Hi All,

I am trying to figure out the best way to fill in blanks/update data in one data table with data form a column in another data table. I have tried the hash/define method, but I couldn't get it to work. So now I'm trying this:

proc sql;
select coalesce (MOBILENO, MOBILE) as MOST_RECENT_MN
from AP;
quit;

I dont really know how to use this function and the doco is over my head.

Basically I would like to select the MOBILENO (when it exists), which is in the AP table and have that overwrite the MOBILE column which is in the M1 table. Where the MOBILENO does not exist then the MOBILE column is fine.

Greatly appreciate any help or direction.

Re: Using coalesce to update data in one column form one table with data in one column form another

SASKiwi — Mon, 27 Jun 2022 04:46:03 GMT

You are on the right track but you've only listed one table in your query. It should look more like this:

proc sql;
create table Want as 
select coalesce (MOBILENO, MOBILE) as MOST_RECENT_MN
from AP
left join M1
on AP.join_column = M1.join_column
;
quit;

I don't know what the joining rules are for your tables so you need to supply that. Putting MOBILENO first in the COALESCE means what will be used if populated, otherwise MOBILE is used.

Re: Using coalesce to update data in one column form one table with data in one column form another

2222 — Mon, 27 Jun 2022 06:06:34 GMT

thank you for that, unfortunately I am getting an ambiguous reference error because MOBILE is in two datasets

Re: Using coalesce to update data in one column form one table with data in one column form another

Kurt_Bremser — Mon, 27 Jun 2022 08:41:51 GMT

In SQL, you need to be clear about where a variable is supposed to come from; define table aliases in the FROM clause and use them when addressing variables.

e.g.

proc sql;
create table want as
  select
    t1.id,
    t1.var_a,
    (t1.var_a - t2.var_b) as diff
  from table_a t1 left join table_b t2
  on t1.id = t2.id
;
quit;

Re: Using coalesce to update data in one column form one table with data in one column form another

2222 — Tue, 28 Jun 2022 01:57:11 GMT

thank you, and does this work if the variable comes from the same table as the variable it is updating? then I drop the variable after i have used it to update the variable that had missing values?

Re: Using coalesce to update data in one column form one table with data in one column form another

Tom — Tue, 28 Jun 2022 02:26:30 GMT

@2222 wrote:
thank you, and does this work if the variable comes from the same table as the variable it is updating? then I drop the variable after i have used it to update the variable that had missing values?

In that case there is no need for SQL at all.

data want;
  set AP;
  most_recent_mn = coalesceC(MOBILENO, MOBILE);
  drop MOBILENO;
run;

Unless you are doing something extremely complex with extremely large datasets it is much better to create a NEW dataset when making changes to the data with code instead of making changes to an existing dataset. That way you can always correct any problems and re-run the code without having to worry that the mistakes have destroyed the source data.

Re: Using coalesce to update data in one column form one table with data in one column form another

2222 — Tue, 28 Jun 2022 04:04:22 GMT

thank you!