CODEWITHSUNDEEP
sqlsas
Anon Li
Anon Li

Reputation: 621

How to check for nulls in SQL/SAS

I have 2 Excel sheets. 

 Excel_Sheet1:  
 subject | T/F?  
 001     |  T  
 002     |  F  

 Excel_Sheet2:  
 subject | SiteId  | ReferenceId
 001     |         |     55
 002     |   44    |     66

Expected output: 

subject | SiteId   | ReferenceId
001     |          |     55

This is what I've done but it doesn't give the right output. I'm currently trying to achieve this through using SQL in SAS, but I'm open to hearing the ways to do this via SAS.

I'm trying to first check if it's True in Excel_sheet1 then check for multiple columns in Excel_sheet2 if all these columns are null. If all these specific columns are null, then return the ReferenceId of 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;

Thanks

Upvotes: 0

Views: 1612

Answers (2)

Parfait
Parfait

Reputation: 107687

Simply align correct data types as subject is a character column (with preceding zeros) and not numeric. Additionally, correct the aliases with s2.SiteId, s2.RecordId in SELECT clause.

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;

Alternatively, use CREATE TABLE ... AS syntax and have column types determined by query:

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;

And alternatives to IS NULL:

WHERE s2.SiteId is missing;

WHERE s2.SiteId = .;

Data

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
;

Output

proc print data = Output; run;

Obs    subject     Id       Id;
1       001        .        55;

Now, if you want a macro variable (not table) output use the INTO clause:

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

Upvotes: 1

josepn
josepn

Reputation: 367

Change WHERE s1.SiteId is Null by WHERE s2.SiteId is Null

Upvotes: 0

Related Questions