Proc sql: replace null values from join

everyone — Wed, 23 Nov 2022 21:11:57 GMT

I'm joining two tables. Within my joined table, a particular field has a null value that I'd like to replace with a non-null value. I'm wondering how I can accomplish this within one sql procedure.

In the below reprex, I create a table that has a null value for id: 3 in the sales field. I'm trying to replace this null value with something like calls*0.1. The only way I know how to do this would be with a case statement in a separate sql procedure ... wondering how I can do this all in one procedure. Thanks!

/*create sample table a*/
data table_a;
input id region $ calls;
cards;
1 south 50
2 north 30
3 west 30
4 south 50
;
run;

/*create sample table b*/
data table_b;
input id sales;
cards;
1 5
3 4
4 6
;
run;

/*join tables a & b*/
proc sql;
create table reprex as
select * 
from table_a as a 
left join table_b as b
on a.id = b.id;
quit;

Re: Proc sql: replace null values from join

Tom — Wed, 23 Nov 2022 21:17:47 GMT

Just do not use the * short cut. Instead actually list the variables you want to select.

create table reprex as
select a.id
     , a.region
     , a.calls
    , coalesce(b.sales,a.calls*0.10) as sales 
from table_a as a 
left join table_b as b
  on a.id = b.id
;

topic Proc sql: replace null values from join in SAS Programming

Proc sql: replace null values from join

Re: Proc sql: replace null values from join