proc sql: join two summary tables

[gzr2mz39]

Hi,

How do I join these two summary tables on the ydoi variable?

The ydoi variable contains the values: 2012, 2013, and 2014 on both summary tables.

proc sql;

create table fall_comb_yr as

select ydoi,

     sum(x) as incid_cnt,

     sum(osha) as osha_cnt,

     sum(dart) as dart_cnt

from classified0

group by ydoi

xxxxxxxx

select ydoi,

     sum(x) as incid_all,

     sum(osha) as osha_all,

     sum(dart) as dart_all

from classified0

group by ydoi

;

run;quit;

Thank you.

[Reeza · Accepted solution]

I don't think you can do it like that, assuming you mean to have XXXXXX be replaced by something.

I also think it may be better to use a standard proc, ie proc mean/summary/univariate to report.

If you absolutely want to use SQL then create each table and use a standard join.

proc sql;

create table fall_comb_yr as

select ydoi,

     sum(x) as incid_cnt,

     sum(osha) as osha_cnt,

     sum(dart) as dart_cnt

from classified0

group by ydoi;

create table fall_all

select ydoi,

     sum(x) as incid_all,

     sum(osha) as osha_all,

     sum(dart) as dart_all

from classified0

group by ydoi

;

create table joined as

select a.*, b.*

from fall_comb_yr a

join fall_all b

on a.ydoi=b.ydoi;

run;quit;

[gzr2mz39]

Thank you.

All that's missing is to add an "as" to this line:

create table fall_all