topic Re: Remove Duplicate Records in SAS Programming

Remove Duplicate Records

BonnaryW — Wed, 06 Sep 2017 22:53:31 GMT

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

Re: Remove Duplicate Records

RahulG — Thu, 07 Sep 2017 04:38:28 GMT

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

Re: Remove Duplicate Records

BonnaryW — Thu, 07 Sep 2017 15:40:59 GMT

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

Re: Remove Duplicate Records

BonnaryW — Thu, 07 Sep 2017 15:57:27 GMT

Hello RahulG,

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

Thank you.

Re: Remove Duplicate Records

MINX — Thu, 07 Sep 2017 20:14:12 GMT

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;

Re: Remove Duplicate Records

BonnaryW — Thu, 07 Sep 2017 22:27:59 GMT

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.

Re: Remove Duplicate Records

ballardw — Thu, 07 Sep 2017 22:47:41 GMT

@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.

Re: Remove Duplicate Records

BonnaryW — Thu, 07 Sep 2017 23:04:59 GMT

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;

Re: Remove Duplicate Records

MINX — Fri, 08 Sep 2017 05:16:14 GMT

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;

Re: Remove Duplicate Records

BonnaryW — Fri, 08 Sep 2017 15:54:47 GMT

THANK YOU and THANK YOU SO MUCH MINX.

It work !!!

Thank you.