topic SQL help in SAS, please! in SAS Procedures

SQL help in SAS, please!

wegweiax — Wed, 09 Apr 2014 00:43:15 GMT

So I am having a problem creating a table in SAS using SQL. I have two files, one that contains files on a firm (which i need information on the Gender of the CEO, which is one of the columns) and a financial ratio which is on the other file (MRET3). The data is on different firms data for 5 consecutive years, this is what i need to do:

I need to average the ROE by firm over those 5 years and then only include companies that have a female CEO.

This is how far I got and I cannot seem to get it to work:

Proc SQL;

Create Table Q11 as

Select Alex.MRET3

Create Table Q11a

Select AVG(MRET3) From Q11 By Alex.year where Jim.gender='FEMA';

Proc Print data=Q11a;

Run;

So as you can see, I am trying to grab MRET from file Alex and create a new table and average them by year but only where the ceo is female.

Please help, I would really appreciate it. If there is anything confusing let me know and I will explain. Thanks

Re: SQL help in SAS, please!

LinusH — Wed, 09 Apr 2014 07:46:19 GMT

The first create table statement seems obsolete.

By should by Group By, and you probably want to have year in your select, otherwise your result will be hard to interpret.

Re: SQL help in SAS, please!

RW9 — Wed, 09 Apr 2014 07:58:48 GMT

Hi,

Something like (and this is guessing without example data):

proc sql;

create table WANT as

select CEO.CEO,

CEO.GENDER,

MRET.AVG_MRET3

from (select distinct CEO,GENDER from CEO where GENDER="Female") CEO

left join (select distinct

CEO,

AVG(MRET3) as AVG_MRET3

from MRET3

group by CEO,

YEAR) MRET

on CEO.CEO=MRET.CEO;

quit;