BookmarkSubscribeRSS Feed
Thylacine
Fluorite | Level 6

Hi, i want to make something like this work in proc sql:

 

proc sql;

%connect_to_sql_macro;

create table sql.table as

(

select some_id, date from connection to oracle

(

select some_id, date, row_number()over(partition by some_id order by date) as row from dataset d join sql_table s on d.some_id=s.some_id 

) where row=1

 

Basically i need to create table in oracle based on dataset joined with oracle table and take the first date for each id.

If i use "from oracle" pass through it doesn't see the dataset, if i make it the other way around i can't use window functions(row_number())  becouse they are not in SAS. For now i create a table from that dataset and then do a pass through but it's large and i need to do it in one go. Any suggestions?

3 REPLIES 3
SASKiwi
PROC Star

The usual approach with this type of problem is to load the SAS table into Oracle as a temporary table first, then use SQL passthru to join to the temporary table. You can't really avoid moving one of the tables into the software environment where you are going to do the processing if they are large as it is the only way the join is going to work efficiently.

Patrick
Opal | Level 21

@Thylacine

With a heterogenous join and if you want to use Oracle specific functions then there is no other way than to first upload the SAS table into Oracle.

 

Why do you need it "in one go"? 

 

You can also upload into a temporary table and you can use bulk-load.

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a003113602.htm

 

As you certainly know data transfer between SAS and the DBMS is most of the time the bottleneck so you want to minimise such transfer. Which way to go depends on your table volumes and cardinality.

 

If that's a regular process and performance is critical then also consider to maintain a permanent table in Oracle for your SAS data and only send updates to this table.... but if and what's possible depends of course on your actual circumstances so just throwing ideas here.

Reeza
Super User

How big is the SAS data set? One option depending on the logic is to move the list of ID's over as macro variables.

Or load the SAS table to your Oracle env.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 710 views
  • 2 likes
  • 4 in conversation