BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BonnaryW
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
MINX
Obsidian | Level 7

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;

View solution in original post

9 REPLIES 9
RahulG
Barite | Level 11

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

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

Hello RahulG,

 

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

 

Thank you.

MINX
Obsidian | Level 7

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
Obsidian | Level 7
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
Super User

@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
Obsidian | Level 7
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
Obsidian | Level 7

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
Obsidian | Level 7

THANK YOU and THANK YOU SO MUCH MINX.

 

It work !!!

 

Thank you.

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
  • 9 replies
  • 1356 views
  • 0 likes
  • 4 in conversation