topic Re: proc sql select distinct except in SAS Programming

proc sql select distinct except

Justin9 — Sun, 27 Jun 2021 23:23:27 GMT

I have the following code to find cities that are in records for May, which are not present in April. If I want to check 'City' values in May that are not present also in February and March, would the correct thing to do be to add on the following before the the quit statement at the end, or is the code to add on different?

except select distinct City from Test where "01Mar2021"d<=MONTH<="31Mar2021"d

except select distinct City from Test where "01Feb2021"d<=MONTH<="28Feb2021"d

proc sql;
        select distinct City
                from Test
                where "01May2021"d<=MONTH<="31May2021"d
        except
        select distinct City
                from Test
                where "01Apr2021"d<=MONTH<="30Apr2021"d
       ;
quit;

Re: proc sql select distinct except

tarheel13 — Mon, 28 Jun 2021 01:37:29 GMT

Yes, you can stack set operators. They would go before the quit statement like you said. You should look at the table that is created to verify you got the desired output.

Re: proc sql select distinct except

ballardw — Mon, 28 Jun 2021 02:45:52 GMT

If the purpose is to get City present in May of 2021 when not present between 01Feb2021 and 30Apr2021 it would be more efficient to use a wider range like

proc sql; select distinct City from Test where "01May2021"d<=MONTH<="31May2021"d except select distinct City from Test where "01FEB2021"d<=MONTH<="30Apr2021"d ; quit;

If you want separate sets of first appearing in a given month I would probably use a different approach involving finding the Minimum (earliest) date that each city appears in the data.

The multiple Excepts would cause more memory overhead and just plain more complicated coding.