Include non existent value to by group in proc sql - SAS

Question

I have a a question with SAS programming.

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

rwc rwc · Accepted Answer · 2021-02-27T05:45:11

I would do the SQL step first, then merge the result with your meta file of all possible origins that you would like to see in the output.

proc sql;
   create table meta (origin char(9));
   insert into meta values('Asia');
   insert into meta values('Australia');
   insert into meta values('Europe');
   insert into meta values('USA');

   create table sample as
      select *
      from meta as m full outer join (
         select *, sum(MSRP) as total_srp
         from sashelp.cars
         group by origin) as s 
      on s.origin = m.origin;


quit;

You would probably have to modify the select statement (avoid wild cards - this code will reorder the columns, make sure you put origin from the meta file - or use the COALESCE function to put them together from both files, etc.).

You should also think about what kind of join is appropriate.

Include non existent value to by group in proc sql - SAS

1 Answers