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!
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;
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.
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;
Thanks! That's helps.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.