BookmarkSubscribeRSS Feed
LizGagne
Obsidian | Level 7

I have a dataset with the following columns:
FiscalYear, EmployeeID, OverallRating

 

I need to find out what employees rated as Ineffective in one FiscalYear are rated in future or previous years.  For example, figuing out how to get the count of employees rated Ineffective in 2013 who were then rated anything else in 2014.

 

I've tried splitting up the data set by year and rating, like this:

PROC SQL;
SELECT COUNT (DISTINCT a.EmployeeID), a.FiscalYear, b.EmployeeID, b.FiscalYear, c.EmployeeID, c.FiscalYear
FROM SY1314_I AS a, SY1415_I AS b, SY1516_I AS c
WHERE c.EmployeeID NOT IN (SELECT EmployeeID FROM SY1314_I) AND NOT IN (SELECT EmployeeID FROM SY1415_I);

but end up with some error messages and no result.

 

I've tried breaking up the data by year and rating and comparing it with IF/THEN statements, like this: 

DATA NotI1415_1516 trash;
	SET SY1314_I SY1415_I SY1516_I;
	IF SY1314_I.EmployeeID = 'Ineffective' AND SY1314_I.EmployeeID NOT IN SY1415_I.EmployeeID AND SY1314.EmployeeID NOT IN SY1516_I.EmployeeID THEN OUTPUT NOTI1415_1516;
ELSE OUTPUT trash;

and also gotten nowhere.

 

Really not sure what to do, any help is appreciated.

5 REPLIES 5
Shmuel
Garnet | Level 18

You can't use in a data step a code like:

IF SY1314_I.EmployeeID = 'Ineffective' AND 
SY1314_I.EmployeeID NOT IN SY1415_I.EmployeeID AND ...

You can refer to a variable of current dataset only.

 

I tested your sql code and changed it a little. Try it now:

PROC SQL;
SELECT COUNT (DISTINCT a.EmployeeID), 
             a.FiscalYear, 
             b.EmployeeID, 
             b.FiscalYear, 
             c.EmployeeID, 
             c.FiscalYear
FROM SY1314_I AS a, 
     SY1415_I AS b, 
     SY1516_I AS c
WHERE c.EmployeeID 
  NOT IN (SELECT distinct EmployeeID FROM SY1314_I, SY1415_I);
quit;

 

 

LizGagne
Obsidian | Level 7

Thanks for responding!  I ran your SQLcode and got "ERROR: Ambiguous reference, column EmployeeID is in more than one table."

 

I tried changing it so that it expicitly referenced a.EmployeeID and b.EmployeeID but then got the error that I can't reference 2 columns in a subquery.

Shmuel
Garnet | Level 18

I haven't noticed but there is no logic to select several employeeIDs especially not 

b.employeeID and c.employeeID as you are looking for those that are not there:

PROC SQL;
SELECT COUNT (DISTINCT a.EmployeeID), 
             a.FiscalYear, 
     /*        b.EmployeeID,  */
             b.FiscalYear, 
     /*        c.EmployeeID,  */
             c.FiscalYear
FROM SY1314_I AS a, 
     SY1415_I AS b, 
     SY1516_I AS c
WHERE c.EmployeeID 
  NOT IN (SELECT distinct EmployeeID FROM SY1314_I, SY1415_I);
quit;

Beyond, I think you should relate to @art297 remarks - and check your logic.

art297
Opal | Level 21

I'm confused! You said you have 3 variables, but then you never reference the 3rd one (employee rating) but make one statement that leads one to think that employeeID is a rating.

 

Some example data and what you really want to achieve would definitely help.

 

Art, CEO, AnalystFinder.com

 

DanielSantos
Barite | Level 11

Hi.

 

Show us an example of the input data.

 

Could be as simple as selecting what you want for each year and intersect everything:

 

proc sql noprint;
create table WANT as

select EmployeeID from EMPLOYEES
where RATED eq 'Ineffective' and FiscalYear eq 2013

intersect

select EmployeeID from EMPLOYEES
where FiscalYear ne 2013

quit;

Daniel Santos @ www.cgd.pt

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 597 views
  • 0 likes
  • 4 in conversation