SQL help in SAS, please!

[wegweiax]

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

[LinusH]

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.

[RW9]

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;