proc sql takes hours to run

[JulieM39]

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;

[Reeza]

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?

[r_behata]

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;

[JulieM39]

thank you