topic proc sql: can I make it for short? in SAS Procedures

proc sql: can I make it for short?

rpg163 — Wed, 16 Nov 2011 12:58:52 GMT

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!

proc sql: can I make it for short?

Ksharp — Thu, 17 Nov 2011 02:34:01 GMT

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.

proc sql: can I make it for short?

QLi — Thu, 17 Nov 2011 14:50:09 GMT

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;

proc sql: can I make it for short?

rpg163 — Sat, 19 Nov 2011 05:13:47 GMT

Thanks! That's helps.