如何在 MS SQL Server 的 WHERE 子句中的 IN 语句上将宏变量传递给 PROC SQL [英] How to pass macro variable to PROC SQL on IN statement in WHERE clause on MS SQL Server

问题描述

我在 MS SQL Server 中有一个表，如下所示:

I have a table in MS SQL Server that looks like:

ID, Code
01, A
02, A
03, B
04, C
...

在 SAS 中定义为

LIBNAME MSSQLDB ODBC 
    CONNECTION=SHAREDREAD 
    COMPLETE='Description=OIPE DW (Dev);DRIVER=SQL Server Native Client 11.0;SERVER=Amazon;Trusted_Connection=Yes;DATABASE=OIPEDW_Dev;'
    SCHEMA='dbo'
    PRESERVE_TAB_NAMES=YES 
    PRESERVE_COL_NAMES=YES;

我有一个 SAS 数据集，它的记录格式与 MSSQLDB(ID 和代码变量)相同，但只是完整数据库的一个子集.

I have a SAS dataset that has records of the same format as MSSQLDB (ID and Code variables) but is just a subset of the full database.

我想执行以下操作:

PROC SQL NOPRINT;
   /* If SASDS contains just codes A and B, CodeVar=A B
   SELECT DISCTINCT CODE INTO :CodeVar SEPARATED BY ' ' FROM SASDS;
QUIT;
/* seplist is a macro that wraps each code in a quote */
%LET CodeInVar=%seplist( &CodeVar, nest=%STR(") );
PROC SQL;
    DELETE * FROM MSSQLDB WHERE CODE IN (&CodeInVar);
    /* Should execute DELETE * FROM MSSQL WHERE CODE IN ('A','B');
QUIT;

问题是这会在 &CodeInVar 宏变量中的值上产生语法错误.

The problem is this generates a syntax error on the values in the &CodeInVar macro variable.

知道如何在 IN 语句中将宏变量值传递给 SQL Server 吗?

Any idea how to pass the macro variable value to SQL Server in the IN statement?

推荐答案

我觉得你这里有几个问题；希望其中一些只是转录错误.

I think you have a few problems here; hopefully some of them are just transcription errors.

首先，这不会做任何事情:

First off, this will not do anything:

PROC SQL;
    DELETE * FROM MSSQLDB WHERE CODE IN (&CodeInVar);
    /* Should execute DELETE * FROM MSSQL WHERE CODE IN ('A','B');
QUIT;

MSSQLDB 是您的库名，而不是表；您需要在此处将其定义为 MSSQLDB.dbname.也许这只是一个复制错误.

MSSQLDB is your libname, not the table; you need to define it as MSSQLDB.dbname here. Perhaps that's just a copying error.

从根本上说，您输入的内容没有任何明显错误.我建议首先确定您的宏变量是否有任何问题.在那里放一个 %put 语句:

Fundamentally there's nothing explicitly wrong with what you've typed. I would suggest first identifying if there are any problems with your macro variable. Put a %put statement in there:

%put &codeinvar.;

看看输出什么.是你想要的吗?如果没有，则修复该部分(大概是宏).

See what that outputs. Is it what you wanted? If not, then fix that part (the macro, presumably).

我想说有很多更好的方法可以做到这一点.首先，您不需要添加宏来添加逗号或引号或任何内容.

I would say that there are a lot of better ways to do this. First off, you don't need to add a macro to add commas or quotes or anything.

PROC SQL NOPRINT;
   /* If SASDS contains just codes A and B, CodeVar=A B */
   SELECT DISCTINCT cats("'",CODE,"'") INTO :CodeVar SEPARATED BY ',' FROM SASDS;
QUIT;

这应该可以让您准确地获得 &codevar [即，'A','B'].

That should get you &codevar precisely as you want [ie, 'A','B' ].

其次，由于您使用的是 LIBNAME 而不是直通 SQL，请考虑使用 SQL 语法而不是完全使用这种语法.

Secondly, since you're using LIBNAME and not passthrough SQL, consider using SQL syntax rather than this entirely.

proc sql;
delete from MSSQLDB.yourtable Y where exists 
  (select 1 from SASDS S where S.code=Y.code);
quit;

有时会更快，具体取决于具体情况(也可能会更慢).如果代码是高频的东西，先用PROC FREQ或者SQL查询总结一下.

That is sometimes faster, depending on the circumstances (it also could be slower). If code is something that has a high frequency, summarize it using PROC FREQ or a SQL query first.

这篇关于如何在 MS SQL Server 的 WHERE 子句中的 IN 语句上将宏变量传递给 PROC SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！