Include not existing value to list of by group in proc sql

[clemz]

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

[PGStats · Accepted solution]

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;

[clemz]

thanks for the tip. I just customized it so that I won't be renaming _origin. 🙂