topic Calculate Retirement in SAS Programming

Calculate Retirement

BrahmanandaRao — Mon, 12 Jul 2021 06:28:01 GMT

Here i want employee retirement date from date_of_birth below find attachment of employee excel sheet

proc import datafile="/home/u35263349/My_Folder/Employee_Details.xlsx"
out=employee_info
dbms=xlsx;
quit;

data ds;
set employee_info;
if  emp_dob >=60 then output;
proc print;
run;

Re: Calculate Retirement

andreas_lds — Mon, 12 Jul 2021 06:54:10 GMT

There are some minor problems with your code:

proc import ends with run; not with quit; - wasn't there a note in the log about this?
you are missing a run-statement at the end of the data-step.

What is emp_dob? If it is the date of birth, and was correctly read by sas, then use function intck to calculate the age in years.

Re: Calculate Retirement

BrahmanandaRao — Mon, 12 Jul 2021 13:09:07 GMT

Please give solution

Re: Calculate Retirement

ballardw — Mon, 12 Jul 2021 14:49:53 GMT

@BrahmanandaRao wrote:

Hi

Here i want employee retirement date from date_of_birth below find attachment of employee excel sheet
proc import datafile="/home/u35263349/My_Folder/Employee_Details.xlsx"
out=employee_info
dbms=xlsx;
quit;

data ds;
set employee_info;
if  emp_dob >=60 then output;
proc print;
run;

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Rule(s) for creating the retirement date are what exactly?

Here is an example of incrementing a DOB to a date 40 years later.

data example;
   dob = '15Mar1955'd;
   retiredate= intnx('year',dob,40,'s');
   format dob retiredate date9.;
run;

Re: Calculate Retirement

andreas_lds — Tue, 13 Jul 2021 05:01:04 GMT

@BrahmanandaRao wrote:
Please give solution

As long as you don't invest the time to provide data in usable form - @ballardw explained why posting excel files is not posting data - i can't provide any code, because i would have to make to many assumptions about types and values in your data, it is most likely that the code would not work with your data at all. Besides i mentioned the function intck, the docs have an example, you should be able to adept that to your needs. Maybe it is not the right function to use, but that depends on the data you have and what you are trying to achieve. Since you have not shown us the expected result, we play another guessing game.

Re: Calculate Retirement

BrahmanandaRao — Tue, 13 Jul 2021 11:47:12 GMT

data emp;
input EMP_CODE 1-5 EMP_NAME $7-14 EMP_DOB 17-26 EMP_DATE_OF_JOINING 27-35 EMP_DESIGNATION $
& 37-57 EMP_CITY $ 62-69 EMP_MOBILE 72-79;
informat EMP_DOB DATE9. EMP_DATE_OF_JOINING DATE9.;
format EMP_DOB DATE9. EMP_DATE_OF_JOINING DATE9.;
datalines;
51123 Angela 22/07/1968 22/05/1990 Manager Mumbai 984511126
51124 Bonnie 02/02/1948 10/11/1994 Clerk Kolkota 984511127
51125 Frank 24/11/1971 02/02/1980 Marketing Coordinator Mumbai 984510178
51126 Joe 01/09/1966 05/05/1990 Medical Assistant Chennai 984516121
51127 Kimberly 26/04/1955 25/07/1990 Web Designer Chennai 984515170
51128 Lisa 27/07/1977 22/04/1990 Dog Trainer Jaipur 984511131
51129 Michael 02/09/1954 04/04/1993 President of Sales Mumbai 984512130
51130 Patrick 09/07/1947 05/08/1992 Nursing Assistant Chennai 984513133
51131 Rose 03/11/1949 06/09/1997 Project Manager Jaipur 984511139
51132 Todd 31/07/1970 20/01/1994 Librarian Hyderabad 984519135
51133 Jhon 01/08/1975 22/10/1999 Project Manager Hyderabad 984511136
51134 Smith 02/08/1968 24/12/1996 Account Executive Kolkota 984511237
;
proc print;
run;

Re: Calculate Retirement

andreas_lds — Wed, 14 Jul 2021 09:46:09 GMT

Have you tested the code?

Logs says

NOTE: Invalid data for EMP_DOB in line 33 17-26.
NOTE: Invalid data for EMP_DATE_OF_JOINING in line 33 27-35.
REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
33         51123 Angela 22/07/1968 22/05/1990 Manager Mumbai 984511126
EMP_CODE=51123 EMP_NAME=Angela 2 EMP_DOB=. EMP_DATE_OF_JOINING=. EMP_DESIGNATION=anager Mumbai 9845111 EMP_CITY=  EMP_MOBILE=.
_ERROR_=1 _N_=1

Re: Calculate Retirement

ballardw — Wed, 14 Jul 2021 17:12:00 GMT

And still no rule(s) for how to actually calculate retirement or show the result.

Some simple POSSIBLE rules:

At a specific calendar age. Show the data that age occurs. Or number of days or weeks or months or years until it occurs.

A specific number of years employed. Show the date that occurs on.

My retirement agency has something they call "Rule 90" for "full retirement benefits". Which is calculated as the date the Number of Years of Service (employed) plus calendar age in years = 90. Not a trivial exercise in programming; not horribly difficult but since you have to simultaneously increment two sets of dates, age and years of employment, not a one line code solution.

So, what would be your rule for calculating retirement and how do you want the solution to show?