Help using Base SAS procedures

proc sql: join two summary tables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 199
Accepted Solution

proc sql: join two summary tables

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.


Accepted Solutions
Solution
‎03-21-2014 02:43 PM
Super User
Posts: 18,549

Re: proc sql: join two summary tables

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;

View solution in original post


All Replies
Solution
‎03-21-2014 02:43 PM
Super User
Posts: 18,549

Re: proc sql: join two summary tables

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;

Regular Contributor
Posts: 199

Re: proc sql: join two summary tables

Thank you.

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

create table fall_all

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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