topic Re: Proc Sql Not Exists Operator in SAS Programming

Proc Sql Not Exists Operator

kisumsam — Mon, 05 Dec 2016 19:53:34 GMT

Hello, got a question about proc sql. I have the following code:

** List of full employee list **;
data emplist;
do empid = 1 to 1000;
first = "XXX";
last = "YYY";
address = "ZZZ";
output;
end;
run;

** List of terminated employee **;
data empterm;
do empid = 1 to 1000;
if ranuni(0)<0.05 then output;
end;
run;

** List of current employee **;
proc sql;
select empid, first, last, address
from emplist
where not exists 
	(select *
		from empterm
		where emplist.empid=empterm.empid);
quit;

The first data step creates a full list of employee. The second data step creates a list of terminated employee.

I'm trying to use the NOT EXISTS operator to create a data set of current employees (full list - terminated). I created the code based on a sample code that I read from the advanced exam study guide but it doesn't seem to be working. It creates the full list of employees including the terminated ones.

Could you anyone show me the problem(s) I have with my code?

Re: Proc Sql Not Exists Operator

Shmuel — Mon, 05 Dec 2016 20:04:25 GMT

I think you mean:

proc sql;
select empid, first, last, address
from emplist
where empid not in
     (select empid from empterm);
quit;

Re: Proc Sql Not Exists Operator

kisumsam — Mon, 05 Dec 2016 20:19:30 GMT

Thanks Shmuel. I think your code works but the exact code I got from the prep guide is:

proc sql;
select lastname, firstname
from sasuser.flightattendants
where not exists
(select *
from sasuser.flightschedule
where flightattendants.empid=
flightschedule.empid);

The code above is supposed to do a, in data step term, "if a and not b merge".

I modified it to test the sample data sets that I created but it didn't work. Do you know why?

Re: Proc Sql Not Exists Operator

Shmuel — Mon, 05 Dec 2016 20:27:18 GMT

I have the feeling that "where not exist" needs a following condition to check,

some expression that is either TRUE or FALSE.

Maybe there is a typo in the guide code. I can't guess.

Re: Proc Sql Not Exists Operator

PGStats — Mon, 05 Dec 2016 22:05:53 GMT

It does work for me:

98   ** List of full employee list **;
99   data emplist;
100  do empid = 1 to 1000;
101  first = "XXX";
102  last = "YYY";
103  address = "ZZZ";
104  output;
105  end;
106  run;

NOTE: The data set WORK.EMPLIST has 1000 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.03 seconds


107
108  ** List of terminated employee **;
109  data empterm;
110  do empid = 1 to 1000;
111  if ranuni(0)<0.05 then output;
112  end;
113  run;

NOTE: The data set WORK.EMPTERM has 46 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds


114
115  ** List of current employee **;
116  proc sql;
117  create table current as
118  select empid, first, last, address
119  from emplist
120  where not exists
121      (select *
122          from empterm
123          where emplist.empid=empterm.empid);
NOTE: Table WORK.CURRENT created, with 954 rows and 4 columns.

124  quit;

Note the number of rows in table CURRENT.

Re: Proc Sql Not Exists Operator

kisumsam — Mon, 05 Dec 2016 22:16:26 GMT

For some reason I just ran it and it worked now. Thanks!