限制 PROC SQL 中的结果 [英] Limiting results in PROC SQL

问题描述

我正在尝试使用 PROC SQL 来查询具有数亿条记录的 DB2 表.在开发阶段，我想对这些记录的任意小子集(比如 1000)运行我的查询.我尝试使用 INOBS 来限制观察结果，但我相信这个参数只是限制了 SAS 正在处理的记录数.我希望 SAS 只从数据库中获取任意数量的记录(然后处理所有记录).

I am trying to use PROC SQL to query a DB2 table with hundreds of millions of records. During the development stage, I want to run my query on an arbitrarily small subset of those records (say, 1000). I've tried using INOBS to limit the observations, but I believe that this parameter is simply limiting the number of records which SAS is processing. I want SAS to only fetch an arbitrary number of records from the database (and then process all of them).

如果我自己编写 SQL 查询，我会简单地使用 SELECT * FROM x FETCH FIRST 1000 ROWS ONLY ...(相当于 SELECT TOP 1000 * FROM x).但是 PROC SQL 似乎没有这样的选项.获取记录需要很长时间.

If I were writing a SQL query myself, I would simply use SELECT * FROM x FETCH FIRST 1000 ROWS ONLY ... (the equivalent of SELECT TOP 1000 * FROM x in SQL Server). But PROC SQL doesn't seem to have any option like this. It's taking an extremely long time to fetch the records.

问题:如何指示 SAS 任意限制要从数据库返回的记录数.

The question: How can I instruct SAS to arbitrarily limit the number of records to return from the database.

我读过 PROC SQL 使用 ANSI SQL，它没有任何关于行限制关键字的规范.也许 SAS 不想努力将其 SQL 语法转换为特定于供应商的关键字?没有解决办法吗?

I've read that PROC SQL uses ANSI SQL, which doesn't have any specification for a row limiting keyword. Perhaps SAS didn't feel like making the effort to translate its SQL syntax to vendor-specific keywords? Is there no work around?

推荐答案

当 SAS 通过 SAS 语法与数据库对话时，可以将部分查询翻译为等效的 DBMS 语言 - 这称为隐式传递.查询的其余部分由 SAS 进行后处理"以产生最终结果.取决于 SAS 版本、DBMS 供应商和 DBMS 版本，在某些情况下甚至某些连接/libname 选项，SAS 语法的不同部分在 SAS 和 DBMS 之间是可翻译的/被认为是兼容的，因此发送到由 DBMS 而不是 SAS 执行.

When SAS is talking to a database via SAS syntax, part of the query can be translated to DBMS language equivalent - this is called implicit pass through. The rest of the query is "post-processed" by SAS to produce final result. Depending on SAS version, DBMS vendor and DBMS version, and in some cases even some connection/libname options, different parts of SAS syntax are translatable/considered compatible between SAS and DBMS and thus sent to be performed by DBMS instead of SAS.

使用 SAS SQL 选项 - INOBS 和 OUTOBS - 我通过不同版本的 SAS 使用 MS SQL 和 Oracle 进行了很多工作，但我还没有看到那些被翻译成 TOP xxx 类型的查询，所以这可能不是还支持，尽管当查询只涉及 DMBS 数据(没有连接到 SAS 数据等)时，应该是完全可行的.

With SAS SQL options - INOBS and OUTOBS - I've worked a lot with MS SQL and Oracle via different versions of SAS, but I haven't seen those ever translated to TOP xxx type of queries, so this is probably not supported yet, although when query touches just DMBS data (no joins to SAS data etc), should be quite doable.

所以我认为你只剩下所谓的显式传递 - 特定的 SAS SQL 语法来连接到数据库.这种类型的查询如下所示:

So I think you're left with the so called explicit pass-through - specific SAS SQL syntax to connect to database. This type of queries look like this:

proc sql;
    connect to oracle as db1 (user=user1 pw=pasw1 path=DB1);
    create table test_table as
    select *
    from connection to db1
        ( /* here we're in oracle */
                  select * from test.table1 where rownum <20 
                )
    ;
    disconnect from db1;
quit;

在 SAS 9.3 中，可以简化语法 - 如果已经存在 LIBNAME 连接，您可以将其重用于显式传递:

In SAS 9.3 the syntax can be simplified - if there's already a LIBNAME connection, you can reuse it for explicit pass-through:

LIBNAME ORALIB ORACLE user=...;

PROC SQL;
connect to oracle using ORALIB;
create table work.test_table as
        select *
        from connection to ORALIB (
....

使用 libname 连接时，请务必在加载数据库时使用 READBUFF(我通常设置为 5000 左右)或 INSERTBUFF 选项(1000 或更多).

When connecting using libname be sure to use READBUFF (I usually set some 5000 or so) or INSERTBUFF options (1000 or more) when loading database.

要查看是否发生隐式传递，请设置 sastrace 选项:

To see if implicit pass-through takes place, set sastrace option:

option sastrace=',,,ds' sastraceloc=saslog nostsuffix;

这篇关于限制 PROC SQL 中的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！