Proc sql on huge data

[SASdevAnneMarie]

Thank you Patrick,

It's SAS Enterprise Guide 7.1, the following code executes correctly, it takes 5 min :

proc sql ;
create table db_evenement_2020 as
select *
from psaeu11.db_evenement
where '01jan2019 00:00:00'dt <= d_compta < '01jan2020 00:00:00'dt
;
quit;

[Patrick]

@SASdevAnneMarie From 5 hours to 5 minutes. That's a happy performance improvement.

SAS Enterprise Guide is your SAS Client. The code you issue with SAS Enterprise Guide gets executed on your SAS Server (Compute). This SAS Server is either a SAS 9.x or SAS Viya version.

As you've experienced right now small changes in a where clause can make a big difference. Even though you've told us otherwise that performance improved that much is an indication that your source table resides in a database or in-memory a CAS table (but CAS is SAS Viya and EG version 7.3 is rather a bit too old for a Viya server instance). 

The only other reason I can think of where performance increases is when a where clause can use an index. The moment you use any function on an indexed column in a where clause, the index can't get used. So also here reformulating the where clause would explain performance improvement - but I strongly doubt that it would go from 5 hours to 5 minutes with an index on a SAS table (unless the SAS table uses the spde or spds engine). 

If you're going to use this table also for other queries then it might be worth you find someone in your organization who can tell you where the source is stored. If you've got access to the SAS Management Client then you could potentially also find out for yourself by looking in the library manager how the pre-assigned library with libref pkt is actually defined and where it points to. 

BTW: If you also want to select 2020 like in your initial post then make sure to change the where clause to  .... <'01jan2021 00:00:00'dt

If you want to improve performance further then don't use select * but list the columns you really want in your select statement.

[ballardw]

---

@SASdevAnneMarie wrote:
Thank you Patrick,

It's SAS Enterprise Guide 7.1, the following code executes correctly, it takes 5 min :

proc sql ;
create table db_evenement_2020 as
select *
from psaeu11.db_evenement
where '01jan2019 00:00:00'dt <= d_compta < '01jan2020 00:00:00'dt
;
quit;

---

 This where clause above calls no functions and is comparing values without any conversions meaning minimum overhead.

where year(datepart(d_compta)) >= 2019
					and year(datepart(d_compta)) <= 2020;

This where clause makes 4 calls to functions for every record; year twice and datepart twice. So 4 times "a huge number of records" is a "huge number of function calls".

Every function call has overhead to execute.

You might see a performance improvement with the following because you are halving the number of function calls.

where 2019 le  year(datepart(d_compta)) le 2020;

[SASdevAnneMarie]

Thank you, Ballardw !

[SASdevAnneMarie]

Sorry, I have a question : my data is like 04/03/1999 09:47:00, when I run the code with the line
where '01jan2019 00:00:00'dt <= d_compta < '01jan2020 00:00:00'dt
it takes much more times beacause I don't have the 00:00:00 in my data.

There is some option when I don't want to analyse 00:00:00 ? I don't want to run the datepart function beacause the execution takes alot of time to run.
Thank you very much !

[Patrick]

There is a difference between the string you see and how SAS deals with SAS datetime values internally. 

SAS Datetime values are nothing else than numerical values representing the count of seconds since 1Jan1960 beginning of the day.

'01jan2019 00:00:00'dt is SAS syntax that will convert the string in quotes into such a numerical value and that's then what SAS uses for the where clause.

If you want to use another datetime string like '04Mar1999 09:47:00'dt things work exactly the same. The string pattern of ddmonyyyy hh:mm:ss is what you must use with this syntax.

If you're dealing with another datetime string pattern then you need the input() function together with an informat - and if there is no suitable informat then you need eventually to modify the text string prior to applying an available informat.

SAS Formats are used to print a SAS Datetime value in human readable form. When dealing with Date and Datetime values always ensure that you understand how the data is actually stored. If you look at the data and you see such a datetime string: If the variable use is of type character then it's just a string, if the variable used in numeric then it's a SAS Date (count of days since 1Jan1960) ore SAS Datetime value and it's the format applied which makes this number show as a data/datetime.