I did this by creating views in databases, I do not have much idea as much as in SAS, but  below are some resources which are interesting and will take you forward.     http://mwsug.org/proceedings/2016/RF/MWSUG-2016-RF10.pdf https://communities.sas.com/t5/Ask-the-Expert/Virtualizing-Enterprise-Data/ta-p/391452   ... View more

can do create final tables in multiple steps in oracle by using global temporary table and then can access those tables outside of connect by using libname method. I have not tested any of this and it has been while I did something like this in oracle. but you will get the idea. I know @SuryaKiran is well versed with oracle pass through. l would like to hear what he thinks on this and what would be his suggestion.   proc sql; connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) (HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234)) (CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" user=xxxxxx pw=xxxxxxxx connection =global); excute( create global temporary table xyz as select distinct A.ID from QUERY A LEFT JOIN QUERY_NEXT B ON A.ID = B.ID) on commit preserve rows; execute(commit) by oracle; /*Is it possible to add code here to export the data back to another location?*/ excute( create global temporary table hello as select * from xyz where name ='whatever') on commit preserve rows; execute(commit) by oracle; /*--------------------------------------------------*/ ); disconnect from ora; quit; /* you can use this temporary table by libname and create your sas table */ libname oradb oracle user=scott pw=tiger path=lupin connection=global dbtemp = yes; proc sql; create table work.table_you_want as select * from oradb.hello ; quit;    ... View more

yes this done easily. below is sample code for teradata but in oracle there is global temporary table which you can use. below code creates 2 temporary tables and then creates final table. please look into the paper given below. even though it is mostly for teradata, can be easily implied to oracle.  http://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-57_Final_PDF.pdf proc sql; connect to teradata (server=myserver user=myuserid pw=mypass connection = global); /* 1st volatile table created*/ execute(create volatile table staging_customer as select * from edwwrkuser.Cusomter table where create_dt between ‘2017-01-01’ and ‘2017-01-31’ ) with data primary index(cust_id)on commit preserve rows) by teradata; execute(commit work) by teradata; /* second Teradata table creation is not shown*/ /* final Teradata table to be created*/ execute(create volatile table Final_cust_txn select a.* , b.txn_id from staging_customer a inner join staging_txn_tbl on a.cust_id =b.cust_id) with data no primary index on commit preserve rows)by teradata; execute(commit work) by Teradata; disconnect from teradata; quit;   ... View more

yes exclusive lock is what I am worried about. It is windows environment without SAS/Share . ... View more

just add drop statement proc sql ; create table want( drop = columns you want) as select a.*, sale1, sale2, sale3, sale1/sale2 as sale1bysale2 format = comma5.3, sale1/sale3 as sale1bysale3 format = comma5.3 from (select store, year, sale, product from have ) a full join (select store, year, product, sum(sale) as sale1 from have group by 1,2,3) b on a.store =b.store and a.year =b.year and a.product =b.product full join (select store, year, sum(sale) as sale2 from have group by 1,2) c on a.store =c.store and a.year =c.year full join (select year, product, sum(sale) as sale3 from have group by 1,2) d on a.product =d.product and a.year =d.year; ... View more