Help using Base SAS procedures

SQL help in SAS, please!

Reply
N/A
Posts: 1

SQL help in SAS, please!

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

Super User
Posts: 5,438

Re: SQL help in SAS, please!

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.

Data never sleeps
Super User
Super User
Posts: 7,993

Re: SQL help in SAS, please!

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;

Ask a Question
Discussion stats
  • 2 replies
  • 196 views
  • 1 like
  • 3 in conversation