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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

2 REPLIES 2
Reeza
Super User

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
Quartz | Level 8

Thank you.

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

create table fall_all

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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