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;
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
;
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
;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.