topic Re: Pro SQL Import merge Quickly in SAS Enterprise Guide

Pro SQL Import merge Quickly

ccaudillo100 — Wed, 25 Jan 2023 18:37:13 GMT

Hello,

I cannot disclose actual examples of data being pulled. Small examples of fake data will be used below.

Currently, I am trying to take a table of 60 IDs and pull out those IDs' connection codes on an import from data tables of 100K+ lines. I am trying to find a way faster way to do this. Currently, it takes 30+ minutes to do this pull.

I DO NOT want to import the database as a whole because this takes hours.

I have tried the codes below and all still take 30 mins.

When I manually put the 60 IDs in for w.id IN () it runs in a matter of 45 seconds.

proc sql;
create table want as
select w.*
From database.tableimport w

where w.indicator = "Y"  and  w.type IN (2 3)
and w.id IN (select id 
from IDList)
;
 quit;

proc sql;
create table want as
select w.*,
id.ID
From ID id
Left Join database.tableimport w on w.id = id.ID
where w.Indicator = "Y"  and w.type IN (2 3);
 quit;

proc sql;
create table want as
select w.*,
id.ID
From ID id
Left Join database.tableimport w on id.ID=w.id 
where w.Indicator = "Y"  and w.type IN (2 3);
 quit;

proc sql;
create table want as
select w.*,
id.ID
From ID id
Join database.tableimport w on id.ID=w.id 
where w.Indicator = "Y"  and w.type IN (2 3);
 quit;

proc sql;
create table want as
select w.*,
id.ID
From ID id
Join database.tableimport w on w.id = id.ID
where w.Indicator = "Y"  and w.type IN (2 3);
 quit;

Any advice is much appreciated

Also, the start table would look like

100

953252453

and Final Table would look like

ID	Indicator	Type	connection
1	Y	2	asdfa
2	Y	3	afsdfae
3	Y	2	avsdfa
100	Y	3	dfad
953252453	Y	2	adfawede
3	Y	2	sadf
1	Y	3	asdfa
953252453	Y	3	asdfaedae

Re: Pro SQL Import merge Quickly

ballardw — Wed, 25 Jan 2023 20:55:11 GMT

Some details that may be important.

Is your Database.tableimport a native SAS dataset or is that a link to an external database?

If it is an external database then you may want to share how you are doing that link as options on that may be what is needed.

Also you need to consider how good/fast your connection to that database might be. If the DB server is busy you may just have to put up with how long it processes your request but there might be options that help optimize performance. But we need to see what you are using.

Re: Pro SQL Import merge Quickly

Tom — Wed, 25 Jan 2023 22:23:45 GMT

If just have 60 values then put them into a macro variable (macro variables can hold up to 64K bytes) and use that to generate the WHERE clause.

proc sql noprint;
select distinct id 
  into :idlist separated by ' ' 
  from ID
;
create table want as
  select w.*
  from database.tableimport w
  where w.indicator = "Y"  and  w.type IN (2 3)
    and w.id IN (&idlist)
;
quit;

If the ID variable is character then add quotes.

select distinct quote(trim(id))
  into :idlist separated by ' ' 
  from ID
;

If you want the quotes to be single quote characters instead of double quote characters so you can use them in explicit passthru SQL then add the second argument to the QUOTE() function. In that case you will probably also have to use comma as the delimiter instead of space.

proc sql noprint;
select distinct quote(trim(id),"'")
  into :idlist separated by ',' 
  from ID
;
connect using database ;
create table want as
  select * from connection to database
(select w.* 
  from schema.tableimport w
  where w.indicator = 'Y'  and  w.type IN (2,3)
    and w.id IN (&idlist)
)
;
quit;

Re: Pro SQL Import merge Quickly

Reeza — Wed, 25 Jan 2023 22:52:32 GMT

Your question implies that the answer to @ballardw question is yes, that one dataset is on a db and one is local. When you mix data between sources like this, SAS imports the full data and then filters. If you use the IN with the hardcoded values, the full query can be sent to the server and it's faster. So you can use @Tom's solution to have SAS 'hardcode' the values for you dynamically and it should work as fast.