topic Exclude missing row from proc sql table output in SAS Programming

Exclude missing row from proc sql table output

Justin9 — Sun, 27 Jun 2021 21:28:44 GMT

Please can someone tell me how I can exclude the missing row from my proc sql table? The code that I have got is as follows, so the output should only show the names (and no blank row in the output).

proc sql;
        create table Records as 
        select distinct Name
                from Test
                where "01May2021"d<=MONTH<="31May2021"d
        except
        select distinct Name
                from Test
                where "01Apr2021"d<=MONTH<="30Apr2021"d
       ;
quit;

Re: Exclude missing row from proc sql table output

SASKiwi — Sun, 27 Jun 2021 21:33:50 GMT

You need to explain what you mean by "blank" or "missing" row. What defines such a row?

Re: Exclude missing row from proc sql table output

Justin9 — Sun, 27 Jun 2021 21:39:19 GMT

My output, 'Records' (see code above) gives the following, so it's row 1 in my example that I would like to exclude (i.e. I only want the distinct Name values to be in the output).

Re: Exclude missing row from proc sql table output

PaigeMiller — Sun, 27 Jun 2021 21:43:50 GMT

proc sql;
        create table Records as 
        select distinct Name
                from Test
                where "01May2021"d<=MONTH<="31May2021"d and not missing(name)
        except
        select distinct Name
                from Test
                where "01Apr2021"d<=MONTH<="30Apr2021"d 
       ;
quit;