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?
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.
I think you mean:
proc sql;
select empid, first, last, address
from emplist
where empid not in
(select empid from empterm);
quit;
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?
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.
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.
For some reason I just ran it and it worked now. Thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: