topic Re: Keep option SQL-Statment in SAS Programming

Keep option SQL-Statment

FK1 — Fri, 03 Jan 2020 08:48:05 GMT

Hello Everyone,

does using the "Keep"-Option make sense at all, when quering a Database-Table? Let's assume, I have a Table named "Table1" which has 100 attributes/variables. However, I am only interested in the first variable/attribute ("var1"). The query I, therefore, use is this:

proc sql;
select var1, count(*) as no_obs format = comma20.
from DB2schema.Table1 (keep= var1)
group by var1;
quit;

When using extended Messaging Options

options dbidirectexec ;
options sastrace=',,,ds' sastraceloc=saslog nostsuffix;
options sql_ip_trace = ALL  msglevel=i;

I get among other information this relevant sentence:

SAS_SQL: Cannot handle dataset options.
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error.

This, to me, suggests, that it is pointless to use the "keep"-option, since the Database cannot handle it, right?

Cheers,

Re: Keep option SQL-Statment

s_lassen — Fri, 03 Jan 2020 08:09:00 GMT

No, in this case the KEEP= option does no good. When you set the data in a data step, you can save some memory and processing time by using the option on the input data, but SQL statements only read the variables that you tell it to. It does no harm either, though.

The KEEP= option may be used with SELECT * in SQL, so that you use the option to limit the variables you want, but I would advise against it in most cases, as explicit variable lists in SQL makes the code easier to read.

The reason you get the messages is because the dataset option is not applicable to the DB2 database, so when you use the option SAS cannot convert the SAS SQL call to native DB2 SQL. It is quite possible that you will get better performance without the KEEP= option, as SAS will then be able to let DB2 handle the whole query.

Re: Keep option SQL-Statment

FK1 — Fri, 03 Jan 2020 08:50:34 GMT

Thank you, @s_lassen , for your reply! It is the nuances, that make the difference!

Re: Keep option SQL-Statment

Tom — Fri, 03 Jan 2020 13:02:45 GMT

Are you sure the message is accurate for the query you posted? Perhaps the message only applied to any checking that SAS did while compiling the statement to see if VAR1 actually existed on dataset TABLE1.

What SQL do you see in the log that SAS actually pushed into the database? Did it push the group by or pull all of the data and group the data on the SAS side?