DATA Step, Macro, Functions and more

Proc Sql Not Exists Operator

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Proc Sql Not Exists Operator

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?

 


Accepted Solutions
Solution
‎12-05-2016 05:16 PM
Respected Advisor
Posts: 4,934

Re: Proc Sql Not Exists Operator

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.

PG

View solution in original post


All Replies
Trusted Advisor
Posts: 1,586

Re: Proc Sql Not Exists Operator

[ Edited ]

I think you mean:

 

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

 

Contributor
Posts: 27

Re: Proc Sql Not Exists Operator

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?

Trusted Advisor
Posts: 1,586

Re: Proc Sql Not Exists Operator

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.

 

Solution
‎12-05-2016 05:16 PM
Respected Advisor
Posts: 4,934

Re: Proc Sql Not Exists Operator

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.

PG
Contributor
Posts: 27

Re: Proc Sql Not Exists Operator

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 2121 views
  • 1 like
  • 3 in conversation