topic Re: proc sql takes hours to run in SAS Enterprise Guide

proc sql takes hours to run

JulieM39 — Tue, 04 Dec 2018 16:28:17 GMT

Hello, I'm trying to run what I thought is some quite simple code, but it's taking hours to complete. I'm trying to create a table in SAS using data from a table in Teradata. There should be around 26 million rows. Does anyone have any idea what the problem could be? I should say that I have only been using SAS for around four weeks and haven't had any training! I'm used to Oracle SQL. My code is here:

%macro creator;

proc sql noprint;

%connect_to_teradata_dna06;

create table TotalData_27NOV18 as

select * from connection to tera

(

select *

from bsp_bd_sd_dna06.kde_cdb_pcust_totaldata_19nov18

);

disconnect from tera;

quit;

%mend;

%creator;

Re: proc sql takes hours to run

Reeza — Tue, 04 Dec 2018 16:33:17 GMT

Does it work for smaller subsets at all? That's not a huge query and should take less than 20 minutes in my experience so I would be checking your network speeds.

Have you tried using a libname instead of sql pass through?

Re: proc sql takes hours to run

r_behata — Tue, 04 Dec 2018 16:44:34 GMT

Check if the underlying Teradata table have any partitions or indexes. Your query is going for a full table scan which is not optimal . Consider splitting your query into smaller subsets as suggested by @Reeza .

To check the Underlying partitions/indexes in you table use the syntax

Show table <table_name>.

Also I would suggest running a sample query to test the waters before running query without filters :

proc sql noprint;

%connect_to_teradata_dna06;

create table TotalData_27NOV18 as

select * from connection to tera

(

select *

from bsp_bd_sd_dna06.kde_cdb_pcust_totaldata_19nov18

sample 1000
);

disconnect from tera;

quit;

Re: proc sql takes hours to run

JulieM39 — Tue, 04 Dec 2018 17:22:18 GMT

thank you