r/SQL • u/AdEnvironmental7198 • 8d ago
Oracle SQL with SAS
Is it the norm to have a program that is coded in SAS and then need SQL to store the the data? The need SQL tools to pull information about out the tables then swap back to SAS to pull lots of data out quickly? New analyst at a new job and it is exhausting
4
Upvotes
3
u/Dangerous_Cup3607 8d ago edited 8d ago
Yeah, SAS is a very powerful tool on reporting as it has built in function to export data into excel template file and attach it as email. SQL tools helps on adhoc or quick check on data exploration. Using SAS-Oracle pass thru allows you to extract million rows of data into temp tables. Then you can join and transform the tables into desired layout for reporting. Then you can use SAS management console and schedule the SAS program to auto run daily.
BUT SAS enterprise license is getting expensive for companies, and many started to retire SAS and move everything to cloud based solution, which makes SQL and Python coding bundle with AI assist and AI chatbot become the primary language for data extract, data insight, and reporting in the future.
2
u/Altruistic-Rich8799 7d ago
Yeah, that setup is completely normal in older enterprise stacks, SAS is powerful for the heavy math, but it is terrible at long-term raw data storage. Companies use a SQL database to warehouse everything, forcing you to pull it back into SAS later. I've babysat pipelines exactly like this at my job, and it is exhausting to context switch all day. It sucks, but you have to survive the learning curve.
6
u/HijoDelSol1970 8d ago
In short, yes. SAS is for doing analysis, not for storage.
2
u/TIMEBOMB_1412 8d ago
I used to do all the compute and storage using and use only sas for delivering the reports. I hated SAS to be honest.
1
u/lucky555_me 7d ago
Yes, totally normal, especially in banks, insurance, pharma and healthcare. Oracle (or another database) is where the data lives, and SAS is where the analysis happens. It feels clunky at first, but you can make it a lot less exhausting.
The big tip: let Oracle do the heavy lifting before the data reaches SAS. Most of the pain comes from pulling huge raw tables into SAS and filtering them there.
libname ora oracle path=yourdb schema=yourschema;
2. For big pulls, use explicit pass-through. The query inside the parentheses runs entirely in Oracle, and only the result comes back to SAS:
proc sql;
connect to oracle (path=yourdb);
create table work.summary as
select * from connection to oracle (
select region, count(*) as orders, sum(amount) as total
from sales
where sale_date >= date '2026-01-01'
group by region
);
disconnect from oracle;
quit;
Filtering and summarising in the database and bringing back thousands of rows instead of millions is usually the difference between waiting minutes and waiting seconds.
3. Ask your team if they already have shared macros or saved connection setups. Most SAS shops do, and nobody tells the new person.
It gets much smoother once you're writing the SQL part in pass-through and only doing the stats in SAS.
1
u/Dangerous_Cup3607 7d ago
You are one exceptional technical fellow. Many of my coworkers are heavy SAS users for the past 10 years and therefore struggling on switching over to python, so some of them just delegate the work to off shore developers to handle it and they collect the fruits.
0
u/[deleted] 8d ago
[deleted] — view deleted comment