Help using Base SAS procedures

proc sql: can I make it for short?

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

proc sql: can I make it for short?

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!


Accepted Solutions
Solution
‎11-17-2011 09:50 AM
Contributor QLi
Contributor
Posts: 57

proc sql: can I make it for short?

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;

View solution in original post


All Replies
Super User
Posts: 9,682

proc sql: can I make it for short?

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.

Solution
‎11-17-2011 09:50 AM
Contributor QLi
Contributor
Posts: 57

proc sql: can I make it for short?

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;

Contributor
Posts: 60

proc sql: can I make it for short?

Thanks! That's helps.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 127 views
  • 3 likes
  • 3 in conversation