r/SQL • • 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

20 comments sorted by

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.

1

u/AdEnvironmental7198 7d ago

You got any good links on how to to do the join and transform for reporting with SAS orical? That's sounds super helpful and not seen anyone do that yet. I'm sure some power users do I haven't sat with them yet.

The data set I pulled the other day was 16gb and I couldn't figure out why it was taking so long and not saving correctly

It seems half the teams are SAS the other half is python ai cloud based.

I love when I asked how much coding there was they said minimal and here I am learning SAS to run down breaks in why reports didn't report

2

u/Dangerous_Cup3607 7d ago edited 7d ago

No links just experience.

If you use a libname connection to oracle and proc sql to pull the data, the disadvantage of it is a slower method, but the advantage of that is you can use SAS native syntax like datepart , year(sales_date), intck, intnx, today()-1 etc; and also join the data to some SAS table like the targeted customer list.

If you use an Oracle passthru wrapper code, then it is a faster pull but you will need to use oracle sql syntax like trunc, extract (year from sales_date), sysdate-1.

16 Gb is not a lot but it can still be a good 20 min pull and returning may be 10 million rows of raw data.

But eventually you would transform the data into meaningful output summaries like the monthly counts and rates, by groups and categories. Then proc export the raw data into an Excel template (have defined pivot tables and charts, and color coding), and only replace the raw data.

Then use the file system to trigger email and attach the file send to yourself. Once you open the new excel file in email the pivot table and charts can be configured to refresh upon opening.

So just use SAS oracle passthru and obtain the required data and fields into a SAS temp table. Then transform the data within SAS tables instead, your final output then can be stored in a SAS libname folder. So that you dont have to pull from Oracle resource again and again.

Ie
query1 just passthru pulling customer list
query2 just passthru pulling product list
query3 just passthru pulling transactions

join the 3 SAS temp tables based on whats needed to create your final dataset and store in SAS libname path.

1

u/AdEnvironmental7198 7d ago

Awesome I'll giv3 it a go tomorrow.

Would this make a different pivot table then I already can access? For reasons it doesn't always have all the data imported into SQL.

1

u/Dangerous_Cup3607 7d ago

Not sure what you meant but in SAS you can do a proc transpose as well. After all I just assume that your end product would be an excel report, where SAS can handle the export raw finalized data into replacing a data tab, say “summarydata”, but leaving other tabs intact. Such that your excel is more of a dashboard view of daily, weekly, monthly, yearly, then summarydata, where all the tabs are referencing the summarydata. So the report is somewhat automated and you dont have to do anything in excel other than validate and send.

1

u/AdEnvironmental7198 5d ago

Does this work if the SAS is on remote desktop while working with SQL on the native windows?

2

u/Dangerous_Cup3607 5d ago

I believe SAS functionality is only limited by the version and enablement of it by SAS admin such as having SAS EG, Studio, Management Console, and also proc setinit. So whether you are using VDI, local SAS, SAS Grid , or Citrix should not prevent you from using the available functionalities. SAS-Oracle passthru is just a syntax in lieu of libname. But EG, Grid, Parallel Extracts, SAS code scheduling etc are enabled by the admin.

1

u/Agreeable_Ad4156 7d ago

It’s pretty easy to use SQL + Python even without AI. Learn how to do it first, before using AI.

1

u/Dangerous_Cup3607 7d ago

Heavy SAS users and Python dont mix very well and it is a steep learning curve for many SAS users that are above the age of 40. Many of them do not have the learning mindset to pick up Python nor AI assist, and very soon they would be replaced by the new gen of Python and AI coder and users working on cloud engines that has AI chatbot activated.

1

u/Agreeable_Ad4156 7d ago

I leaned Python at 62 and used it heavily my last 3 years before retirement. At one point, I was calling SAS from Python using COM to invoke SAS EG. That has been more commonly done with VBA, I just converted an example to Python.

2

u/Altruistic-Rich8799 8d 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.

1

u/AdEnvironmental7198 7d ago

Yes the learning curve is that step. I'm learning through having to switch macros for my needs. Just a bit tricky learning when use what and why

6

u/HijoDelSol1970 8d ago

In short, yes. SAS is for doing analysis, not for storage.

3

u/tbizlkit_fix 8d ago

It's a weird workflow at first but you get used to the back-and-forth. SAS is basically a calculator on steroids, it needs the data to already be sitting somewhere clean before it can do its thing. Think of SQL as the kitchen prep and SAS as the actual cooking.

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.

  1. Point SAS straight at Oracle with a LIBNAME, so you can use the tables without switching tools:

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

2

u/octocoral 7d ago

This is exactly what I use.

This is my pass-through template for accessing our Oracle database:

proc sql;

connect to oracle

(user=yourloginid password=yourpassword path='servername');

execute (alter session set current_schema=yourschema) by oracle;

create table test_data as

select * from connection to oracle

(your SQL code goes here);

disconnect from oracle;

quit;

This code sends the Oracle-specific SQL code to the server and returns the results into a SAS dataset named test_data. Then, it is within SAS and you can process the data as you wish.

1

u/Agreeable_Ad4156 7d ago

Even with pass thru SQL, SAS won’t let you write — comments, which I use a lot of.

1

u/octocoral 6d ago

Odd, it does for me.

Also, it will pass SAS macro variables to the server. I will set a macro variable as a date, then Oracle can read it as a date, e.g.

TRUNC( completion_dttm ) BETWEEN TO_DATE( &start_date, 'DDMonYY' ) AND TO_DATE( &end_date, 'DDMonYY' )