proc sql: can I make it for short?

[rpg163]

Hi,

I have several sql queries, and I am wondering if I can make it for short

the code goes like this

proc sql;

    create table finrep as

    select    A.*, (A.Assets+B.Assets)/2 as Assets_avg

    from    fin_info as A, fin_info_s as B

    where    A.stkcd=B.stkcd and A.year=B.year;

quit;

proc sql;

    create table asd1 as

    select    A.*,    B.ind

    from    finrep as A left join industry as B

    on    A.stkcd=B.stkcd;

quit;

proc sql;

    create table asd2 as

    select A.*,B.IPO_date

    from    asd1 as A left join IPO as B

    on A.stkcd=B.stkcd;

quit;

proc sql;

    create table asd3 as

    select *

    from asd2 as A left join control as B

    on A.stkcd=B.stkcd and A.year=B.year;

quit;

proc sql;

    create table asd4 as

    select *

    from asd3 as A left join str_dummy(drop=STR07 STR08) as B

    on A.stkcd=B.stkcd;

quit;

can I make it within one sql procedure?

THX!

[Ksharp]

It is hard to suggest somthing without seeing the sample data and each output of proc sql  you want .

You can use sub-query or insert-view to simple it.

BTW, You only need one proc sql; quit; It is run-group ,knows all the sql statements in it.

[QLi · Accepted solution]

Maybe you can merge Proc Sql  into one or two, 

proc sql;

    create table finrep as

    select    A.*, (A.Assets+B.Assets)/2 as Assets_avg,C.IND,d.IPO_date,e.*, f.*

    from    fin_info as A INNER JOIN fin_info_s as B,

    ON    A.stkcd=B.stkcd and A.year=B.year

LEFT JOIN industry as C ON A.stkcd=C.stkcd

LEFT JOIN IPO AS d ON A.stkcd=d.stkcd

LEFT JOIN control AS e ON A.stkcd=e.stkcd AND A.year=e.year

LEFT JOIN str_dummy(drop=STR07 STR08) as f ON A.stkcd=f.stkcd;

quit;

[rpg163]

Thanks! That's helps.