How to caculate years and find specific variable

[ydu180]

Hi all,

I need to find retirees who have worked for 25 or more years for the airline company. If you want to, you can use December 31, 2016 as a point of reference.

My data step is :

data hw3.retirees25;
infile "&path/retirees2017.txt" dlm=' ';
length LastName $ 10 FirstName $ 11 HD $ 10;
input Department$12. HD$ ES ID$ LastName$ FirstName$;
label HD="Hire Date"
         ES="Ending Salary"
         ID="EmployeeID";
years=int(yrdif(HD,12/31/2016,'ACT/365'));
where years >= 25;
run;

However, it shows: ERROR: No input data sets available for WHERE statement.

Could anyone help me how I could find those people work over 25 years?

[Astounding]

When you are reading from raw data (INFILE / INPUT), the WHERE statement is illegal.  Just change WHERE to IF.

[Reeza]

'31Dec2016'd

You specify dates in SAS as a date literal, see above. Replace your date in the INTCK function with a date in the format above. 

[Ksharp]

First of all . Function yrdif() require DATE type arguments.  But yours is character type.  


data hw3.retirees25;
infile "&path/retirees2017.txt" dlm=' ';
length LastName $ 10 FirstName $ 11 HD $ 10;
input Department$12. HD  : mmddyy12.  ES ID$ LastName$ FirstName$;
label HD="Hire Date"
         ES="Ending Salary"
         ID="EmployeeID";
years=int(yrdif(HD,'31dec2016'd,'ACT/365'));
if  years >= 25;
run;   

[ydu180]

After I changed that, I could not print out in the next part. I must let the date shows in character way, it is the original one I did not asked to change that. So is there another way, I could caculate years between them and only show years >= 25?

[Astounding]

So far, you're doing the right thing.  The way you print a date in as "characters" is to apply a format.  This statement could become part of the DATA step, or it could become part of the next step that prints the data (either way is fine):

format hd mmddyy10.;

There are many other date formats you can choose from.  Here are just a few samples:

format hd date9.;

format hd yymmdds10.;

[ydu180]

data hw3.retirees25;
   infile "&path/retirees2017.txt" dlm=' ';
   length LastName $ 10 FirstName $ 11 HD $ 10;
   if HD <= "12/31/1991";
   input Department$12. HD$ ES ID$ LastName$ FirstName$; 
   label HD="Hire Date"
         ES="Ending Salary"
         ID="EmployeeID";
   format ES comma7.; 
run;

I tried another way, but it still show all my observations.

[andreas_lds]

Comparing dates in strings will most likely not give the expected result. The subetting if-statement ist misplaced, move it below the input statement.