Remove Duplicate Records

[BonnaryW]

Hello,

I need to remove record if Name, and Job_Date are the same, and calculate only the CALC field value is "YES" for each Name stay at their title.  For example for EE10, want to calculate Job_date of 16JAN2009 to Job_date 01NOV2011, then we want to calculate Job_date 07DEC2015 to today's date. Thank you for your any assistance.

EE10    SPEC V,HR                                       01MAY2001 
EE10   PROJ MGR                                        01JAN2004 
EE10   SUPV IV,ADMIN                               16JAN2009        YES
EE10  PROJ MGR                                        01NOV2011 
EE10  CONSL,HR STRATEGY & GOVER  06JAN2014 
EE10  MGR I,HR                                         07DEC2015        YES
EE10  MGR I,HR                                        07DEC2015         YES

Name     Title                                                     JOB_DATE    CALC

EE8        SUPV V                                               01AUG2011   YES
EE8       ENGINEER IV,ELECT                        10DEC2012 
EE8       MGR I,SYS CONTROL CTR SUP     02MAR2015     YES
EE8       MGR I,SYS CONTROL CTR SUP     02MAR2015     YES
EE9      GENL MGR,DIVERSIFIED PLAN      11NOV2013      YES
EE9      VP,POWER SUPPLY GENERATIO  31AUG2015      YES
EE9      VP,POWER SUPPLY GENERATIO  31AUG2015      YES
EE10    SPEC V,HR                                       01MAY2001 
EE10   PROJ MGR                                        01JAN2004 
EE10   SUPV IV,ADMIN                               16JAN2009        YES
EE10  PROJ MGR                                        01NOV2011 
EE10  CONSL,HR STRATEGY & GOVER  06JAN2014 
EE10  MGR I,HR                                         07DEC2015        YES
EE10  MGR I,HR                                        07DEC2015         YES

[RahulG]

It is not very clear what you needed as an output. Please elaborate

[BonnaryW]

Hello RahulG,
Thank you so much for looking at post. Please see below.
[cid:image001.jpg@01D327B4.F5C1ED40]
Thank you.

[BonnaryW]

Hello RahulG,

Thank you for your assistance, Please see attachement for the end result.  

Thank you.

[MINX]

Use proc sort with nodupkey option to remove duplicate.

Samples code as below:

/*Remove duplicated data of name and JOB_DATE */

proc sort data=mgrdata nodupkey;

by name JOB_DATE;

run;

/*Calculate YRS when CALC='YES'*/

data mgrdata1;

length YRS $40;

set mgrdata;

if CALC = 'YES' then do;

YR = floor((today() - JOB_DATE)/3600);

MT = ((today() - JOB_DATE)/3600 - floor((today() - JOB_DATE)/3600))*12;

YRS = strip(put(YR,best.))||'yrs '||strip(put(MT,2.0))||'mos';

end;

drop YR MT;

run;

/*sort back with original order */

proc sort data=mgrdata1;

by name title JOB_DATE;

run;

[BonnaryW]

Thank you for responding to my post. The NODUPKEY worked great. The calculation is not working, I believed the year seem to be off.
For EE10 records,

EE10 SPEC V,HR 01MAY2001

EE10 PROJ MGR 01JAN2004

EE10 SUPV IV,ADMIN 16JAN2009 YES 2yrs 9mos

EE10 PROJ MGR 01NOV2011

EE10 CONSL,HR STRATEGY & GOVER 06JAN2014

EE10 MGR I,HR 07DEC2015 YES 1yr 9mos

For 2yrs 9mos, I took 01NOV2011 subtract with 16JAN2009
For 1yr 9mos, I took today date subtract with 07DEC2015.
Please assist and thank you so much.

[ballardw]

---

@BonnaryW wrote:
Thank you for responding to my post. The NODUPKEY worked great. The calculation is not working, I believed the year seem to be off.

For 2yrs 9mos, I took 01NOV2011 subtract with 16JAN2009
For 1yr 9mos, I took today date subtract with 07DEC2015.
Please assist and thank you so much.

---

Show the code you used, the expected values and the actual values obtained.

[BonnaryW]

Hello, below is my code.

PROC SORT DATA=FINAL NODUPKEY; BY NAME JOB_DATE;
RUN;

DATA CALC;
LENGTH yrs $40;

SET FINAL;

IF CALC='YES' THEN DO;
YR = floor((today() - JOB_DATE)/3600);
MT = ((today() - JOB_DATE)/3600 - floor((today() - JOB_DATE)/3600))*12;
YRS = strip(put(YR,best.))||'yrs '||strip(put(MT,2.0))||'mos';
end;
drop YR MT;
run;

PROC SORT; BY NAME JOB_DATE;

PROC PRINT D ;ID NAME TITLE JOB_DATE;
VAR CALC YRS;
BY NAME
RUN;

[MINX · Accepted solution]

Using look-Ahead and Look-Back method as below from http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

/*Generate nex Job date*/
data mgrdata1;
set mgrdata;
by name;
set mgrdata ( firstobs = 2 keep = JOB_DATE rename = (JOB_DATE = NEXT_JOB_DATE))
mgrdata ( obs=1 drop=_all_);
NEXT_JOB_DATE = ifn( last.name, today(), NEXT_JOB_DATE );
run;

/*Calculate YRS when CALC='YES'*/
data mgrdata1;
set mgrdata1;
if CALC = 'YES' then do;
years = (NEXT_JOB_DATE - JOB_DATE)/365.25;
YR = floor(years);
MT = (years - YR)*12;
YRS = strip(put(YR,best.))||'yrs '||strip(put(MT,2.0))||'mos';
end;
run;

[BonnaryW]

THANK YOU and THANK YOU SO MUCH MINX.

It work !!!

Thank you.