topic Re: Proc sql on huge data in SAS Programming

Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 08:59:09 GMT

Hello Experts,

My data is very huge, it takes 5 hours to run the code. Do you know some tricks to avoid this please ?

My code is :

proc sql;
	create table event2020 as
		select *
			from pkt.event
				where year(datepart(d_compta)) >= 2019
					and year(datepart(d_compta)) <= 2020;
quit;

Thank you very muche for your help !

Re: Proc sql on huge data

LinusH — Wed, 30 Jun 2021 09:26:08 GMT

Have d_compta indexed
Use dt intervals instead of function call in the where cluase

Re: Proc sql on huge data

LinusH — Wed, 30 Jun 2021 09:35:10 GMT

Furthermore, it might not be the filtering that is causing execution time. Performance is about the whole chain of data on disk, movement to memory and CPU ussage.

Is your data in a Base SAS libname? What kind of storage?

Add these options to better understand your bottlenecks:

options fullstimer msglevel=i;

Re: Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 10:02:58 GMT

Thank you, LinushH!
What do you mean by " d_compta indexed" and "Use dt intervals" please ?

Re: Proc sql on huge data

Patrick — Wed, 30 Jun 2021 12:18:22 GMT

First thing that jumps to my mind: Is your source table a SAS table or in a data base? Where does libref pkt point to?

If it's a database then most likely the where condition doesn't get pushed to the database (because of the use of the datepart() function) but all the data gets first transferred over the network to SAS and then only filtered on the SAS side.

You could run below two queries to test the theory.

The 2nd query will push the where condition to the data base for execution (if your source table is in a database) so if the 2nd query is much faster then the required cause of action is clear.

IF the source table is in a database then the options I've added will show you in the SAS log which portion of the query gets pushed to the db for execution.

options fullstimer msglevel=i;
options sastrace=',,,d' sastraceloc=saslog nostsuffix;

libname pkt list;

proc sql;
	create table test_1 as
		select d_compta
			from pkt.event
				where year(datepart(d_compta)) = 2019
				;
quit;

proc sql;
	create table test_2 as
		select d_compta
			from pkt.event
				where '01jan2019 00:00:00'dt <= d_compta < '01jan2020 00:00:00'dt
				;
quit;

Re: Proc sql on huge data

Ksharp — Wed, 30 Jun 2021 12:25:52 GMT

Or try this one :

proc sql;
create table event2020 as
select *
from pkt.event
where year(datepart(d_compta)) in (2019 2020);
quit;

Or event try data step like :

options cpucount=8 threads compress=yes bufno=100 bufsize=128k ;
data event2020;
set pkt.event;
if year(datepart(d_compta)) in (2019 2020);
run;

P.S. I would expect data step is faster than SQL .

Re: Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 12:34:54 GMT

Thank you, Ksharp ! The bufno=100 bufsize=128k is it maximum size please ?

Re: Proc sql on huge data

Ksharp — Wed, 30 Jun 2021 12:43:00 GMT

No. Actually I steal it from @ChrisNZ .

From his test, it looks like "bufno=100 bufsize=128k " is the best configure for running speed .

But you could ask @ChrisNZ

Re: Proc sql on huge data

Patrick — Wed, 30 Jun 2021 12:46:04 GMT

@SASdevAnneMarie

So you're 100% sure that your source table is already a SAS table? If you execute...

libname pkt list;

...does that show you a pathname as the location in the SAS log or something else?

Re: Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 12:54:53 GMT

Thank you for your message. I see this:
Libref= PKT
Scope= IOM ROOT COMP ENV
Engine= META
Access= READONLY
Physical Name= PKT
Schema/Owner= PEGASE

Re: Proc sql on huge data

Patrick — Wed, 30 Jun 2021 13:02:27 GMT

Bugger! It uses the Meta engine so you would need to use SAS SMC and look in SAS metadata how the library is actually defined and where it points to.

You could execute below. That shouldn't take too much time. The log will then tell you if the actual table is in a database. If it's in a database you'll see in the log some trials where SAS tells you which SQL syntax it sent to the database to test if it can execute it, and then also a message telling you which SQL has been chosen to be sent in the end.

...and if it's a SAS table then you won't have any of these "strange" messages in your SAS log.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc sql outobs=100;
	create table test_2 as
		select d_compta
			from pkt.event
				where '01jan2019 00:00:00'dt <= d_compta < '01jan2020 00:00:00'dt
				;
quit;

Re: Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 13:07:09 GMT

Thank you Patrick,
I don't have any "strange" message in my SAS log.

Re: Proc sql on huge data

Ksharp — Wed, 30 Jun 2021 13:07:14 GMT

Patrick,
If this table (pkt.event) was under Data Base, why not using Pass-Through SQL to let DB do the work?

Re: Proc sql on huge data

Patrick — Wed, 30 Jun 2021 13:16:05 GMT

@Ksharp That's exactly where I was heading to. The query @SASdevAnneMarie just executed together with the options would a) have pushed the where clause to the db and b) the SAS log would have told us that this is happening. But from what Marie tells us now it really looks like a SAS table.

@SASdevAnneMarie Is this SAS 9.4 or SAS Viya? And how many rows and columns does your source SAS table have? Like: Can you share the result of a Proc Contents? 5+ hours for such a simple query that doesn't require any sorting feels excessive.

Re: Proc sql on huge data

SASdevAnneMarie — Wed, 30 Jun 2021 13:35:35 GMT

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;

Re: Proc sql on huge data

Patrick — Wed, 30 Jun 2021 14:00:22 GMT

@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.

Re: Proc sql on huge data

ballardw — Wed, 30 Jun 2021 17:08:24 GMT

@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;

Re: Proc sql on huge data

SASdevAnneMarie — Fri, 02 Jul 2021 07:45:28 GMT

Thank you, Ballardw !

Re: Proc sql on huge data

SASdevAnneMarie — Fri, 02 Jul 2021 09:41:34 GMT

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 !

Re: Proc sql on huge data

Patrick — Sat, 03 Jul 2021 03:15:29 GMT

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.