topic Re: proc sql: join two summary tables in SAS Procedures

proc sql: join two summary tables

gzr2mz39 — Fri, 21 Mar 2014 18:27:53 GMT

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.

Re: proc sql: join two summary tables

Reeza — Fri, 21 Mar 2014 18:43:08 GMT

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;

Re: proc sql: join two summary tables

gzr2mz39 — Fri, 21 Mar 2014 19:21:03 GMT

Thank you.

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

create table fall_all