SAS Proc SQL数据库表插入 [英] SAS Proc SQL Database Table Insert

问题描述

使用SAS的Proc SQL，是否可以将SAS数据集中的记录插入到打开的SQL Server连接中的表中?像这样的东西(不起作用):

Using SAS's Proc SQL, is there a way to insert records from a SAS Dataset into a table in the open SQL Server connection? Something like this (which doesn't work):

proc sql exec;
    connect to sqlservr as DataSrc (server=my-db-srvr database=SasProcSqlTest);

    create table Items as select * from connection to DataSrc (
        SELECT * FROM tblItem
    );

    update Items
    set Name = Name + Name,
        Value * 2;

    insert into tblItem (Name, Value)
    select Name, Value
    from Items;

    disconnect from DataSrc;quit;run;
quit;
run;

推荐答案

据我所知，使用直通SQL会将您约束到数据库服务器. SAS文档要求您最好创建对数据库的库引用，然后像对待SAS表一样对待数据库表.在您的情况下，这意味着仅是普通的proc sql.这至少应该在最新的SAS版本中有效，但是对于大表而言并不是最佳选择.

To my knowledge, using pass through SQL constrains you to the database server. The SAS documentantion says that you should preferrably create a library reference to the database and then treat the database tables just like SAS tables. In your case this means just normal proc sql. This should work at least in the latest SAS versions, but for large tables is not optimal.

我们为避免这种情况所做的是

What we've done to circumvent this is

1. 在临时数据库中创建表-该表不应是特定于会话的
2. 使用proc append将数据从SAS批量加载到创建的表中
3. 进行更新
4. 将表拖放到临时数据库中.

这篇关于SAS Proc SQL数据库表插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！