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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.