如何在 SQL/SAS 中检查空值

Question

我有 2 个 Excel 工作表。

 Excel_Sheet1:  
 subject | T/F?  
 001     |  T  
 002     |  F  
 Excel_Sheet2:  
 subject | SiteId  | ReferenceId
 001     |         |     55
 002     |   44    |     66

预期产出：

subject | SiteId   | ReferenceId
001     |          |     55

---

这就是我所做的，但它没有给出正确的输出。我目前正在尝试通过在 SAS 中使用 SQL 来实现这一目标，但我愿意听取通过 SAS 执行此操作的方法。

我尝试首先检查它是否为 True Excel_sheet1然后检查Excel_sheet2中的多列是否所有这些列都为 null。如果所有这些特定列都为 null，则返回 ReferenceId Excel_sheet2

/*
 *  Import files Excel_sheet1 as s1, Excel_sheet2 as s2
 */

proc sql;
/*
 *  Create table and add 
 */
CREATE table output (
    subject int, 
    SiteId int,
    RecordId int
);

INSERT INTO output
SELECT distinct s1.subject, s1.SiteId, s1.RecordId 
FROM mylib.sheet1 as s1 FULL OUTER JOIN mylib.sheet2 as s2 
ON s1.subject = s2.subject
WHERE s2.SiteId is Null
;
quit;

/* export as excel */
%put &output;   

谢谢

Answer 1

只需对齐正确的数据类型，因为主题是字符列(前面有零(而不是数字。此外，在 SELECT 子句中使用 s2.SiteId, s2.RecordId 更正别名。

proc sql;
    /*
     *  Create table and add 
    */
    CREATE TABLE output (
        subject varchar(3),   
        SiteId int,
        RecordId int
    );
    INSERT INTO output
    SELECT distinct s1.subject, s2.SiteId, s2.ReferenceId 
    FROM Excel_Sheet1 as s1 
    FULL OUTER JOIN Excel_Sheet2 as s2 
    ON s1.subject = s2.subject
    WHERE s2.SiteId is Null;
quit;

或者，使用CREATE TABLE ... AS语法，并由查询确定列类型：

proc sql;
    CREATE TABLE output as
    SELECT distinct s1.subject, s2.SiteId, s2.ReferenceId 
    FROM Excel_Sheet1 as s1 
    FULL OUTER JOIN Excel_Sheet2 as s2 
    ON s1.subject = s2.subject
    WHERE s2.SiteId is Null;
quit;

和IS NULL的替代品：

WHERE s2.SiteId is missing;
WHERE s2.SiteId = .;

数据

data Excel_Sheet1;
    infile datalines delimiter=',' DSD; 
    length subject $ 3;
    length T_F $ 1;
    input subject $ T_F $;
    datalines;
001,T
002,F
;
data Excel_Sheet2;
    infile datalines delimiter=',' DSD; 
    length subject $ 3;
    input subject $ SiteId ReferenceId;
    datalines;
001,,55
002,44,66
;

输出

proc print data = Output; run;
Obs    subject     Id       Id;
1       001        .        55;

---

现在，如果你想要一个宏变量(不是表(输出，请使用INTO子句：

proc sql;
    SELECT distinct s2.ReferenceId 
    INTO :output
    FROM Excel_Sheet1 as s1 
    FULL OUTER JOIN Excel_Sheet2 as s2 
    ON s1.subject = s2.subject
    WHERE s2.SiteId = .;   *is missing;
quit;
%put &output.;  
55

Answer 2

更改 其中 s1.SiteId 为空由 其中 S2.SiteId 为空