BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
clemz
Calcite | Level 5

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

 

  

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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;

 

PG

View solution in original post

2 REPLIES 2
PGStats
Opal | Level 21

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;

 

PG
clemz
Calcite | Level 5
thanks for the tip. I just customized it so that I won't be renaming _origin. 🙂

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 931 views
  • 0 likes
  • 2 in conversation