Hi,
I have a table for example sashelp.cars, and I want to group them by origin and total their MSRP.
Question is, what if I want to add 'Australia' in the list of by group but in the sashelp.cars, there are no make/model that originated from Australia?
What I did is to use proc sql:
proc sql; create table sample as select *, sum(MSRP) as total_srp from sashelp.cars group by origin; quit;
But in output dataset sample, I only see 'Asia' 'Europe' and 'USA' since they are the only one's available from sashelp.cars.
Is there a more straightforward way to include Australia in the output dataset sample with 0 total_srp even though it's not existing before the by group?
What I'm thinking is having a meta table with complete list of Origin: 'Asia' 'Europe', 'USA" and "Australia" and then look-up into the sashelp.cars so that it will have a 'dummy' value before doing the proc sql.
Thanks in advance
You are on the right track, all you need is a left join:
data origins;
length _origin $10;
do _origin = "Asia", "Europe", "USA", "Australia";
output;
end;
run;
proc sql;
create table sample as
select
o._origin,
c.*,
coalesce(sum(MSRP), 0) as total_srp
from
origins as o left join
sashelp.cars as c on o._origin = c.origin
group by o._origin;
quit;
You are on the right track, all you need is a left join:
data origins;
length _origin $10;
do _origin = "Asia", "Europe", "USA", "Australia";
output;
end;
run;
proc sql;
create table sample as
select
o._origin,
c.*,
coalesce(sum(MSRP), 0) as total_srp
from
origins as o left join
sashelp.cars as c on o._origin = c.origin
group by o._origin;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.