Hello,
I have this data and would like to get the output as bellow:
ID TITLE DATE MANAGER
EE1 JOB1 01/01/2013 SUE
EE1 JOB2 01/01/2016 JAN
EE1 JOB3 06/30/2017 JAN
EE1 JOB4 11/20/2017 BOB
EE2 JOB1 01/30/2015 JOHN
EE2 JOB2 05/30/2016 JANET
EE2 JOB3 06/30/2017 DOUG
EE3 JOB1 03/15/2017 DOUG
EE4 JOB1 10/15/2016 JAN
EE4 JOB2 11/01/2017 CURT
I like to result to show as below and only records with multiple title.
ID PTITLE PDATE PMANGER TITLE DATE MANGER
EE1 JOB1 01/01/2013 SUE JOB2 01/01/2016 JAN
EE1 JOB2 01/01/2016 JAN JOB3 06/30/2017 JAN
EE1 JOB3 06/30/2017 JAN JOB4 11/20/2017 BOB
EE2 JOB1 01/30/2015 JOHN JOB2 05/30/2016 JANET
EE2 JOB2 05/30/2016 JANET JOB3 06/30/2017 DOUG
EE4 JOB1 10/15/2016 JAN JOB2 11/01/2017 CURT
Thank you for any help
data a;
input @1 ID $3. @5 TITLE $4. @10 EDATE DATE9. @20 MANAGER $6.;
format edate date9.;
datalines;
EE1 JOB1 01JAN2013 SUE
EE1 JOB2 01JAN2016 JAN
EE1 JOB3 30JUN2017 JAN
EE1 JOB4 20NOV2017 BOB
EE2 JOB1 30JAN2015 JOHN
EE2 JOB2 30MAY2016 JANET
EE2 JOB3 30JUN2017 DOUG
EE3 JOB1 15MAR2017 DOUG
EE4 JOB1 15OCT2016 JAN
EE4 JOB2 01NOV2017 CURT
;
RUN;
data temp;
merge a(rename=(title=ptitle edate=pdate manager=pmanager))
a(firstobs=2 drop=id);
run;
data want;
set temp;
by id;
if last.id then delete;
run;
Note: code fixed. Sorry for that .
@BonnaryW wrote:
Hello,
I have this data and would like to get the output as bellow:
ID TITLE DATE MANAGER
EE1 JOB1 01/01/2013 SUE
EE1 JOB2 01/01/2016 JAN
EE1 JOB3 06/30/2017 JAN
EE1 JOB4 11/20/2017 BOB
EE2 JOB1 01/30/2015 JOHN
EE2 JOB2 05/30/2016 JANET
EE2 JOB3 06/30/2017 DOUG -> Why is this not included in the results? This doesn't look quite like a 'duplicate' find process so it would help if you explained your logic in more detail.
EE3 JOB1 03/15/2017 DOUG
EE4 JOB1 10/15/2016 JAN
EE4 JOB2 11/01/2017 CURT
I suspect you can try a SQL join here with a cross join and filter on where titles don't match but ID's do.
UNTESTED:
proc sql;
create table want as
select t1.*, t2.title, t2.date
from have as t1
join have as t2
on t1.id=t2.id and t1.title < t2.title;
quit;
Thank you so much for your quick response, Reeza. The reason why ID EE3 is not listed, because there is not title change. I like the output to list ID that have title changed.
Thank you so much/
Title is a the variable 'title' correct? Then it did change form the previous value....or did it not change because that's the latest record and there's no record after it?
The logic here is really unclear.
I think I get it now - you have a slowly changing dimension and you're trying to track what the title changed to.
I think you're better off using a DATA STEP then instead and using the LAG function. Again, untested.
data want;
set have;
by id;
prev_title= lag(title);
prev_date = lag(date);
prev_manager = lag(manager);
if last.id and first.id then do; call missing(prev_title, prev_date); end; *records no output though so not necessary;
else if not last.id then do;
output;
end;
run;
There’s no attachment or image if thats what you were trying to do.
data a;
input @1 ID $3. @5 TITLE $4. @10 EDATE DATE9. @20 MANAGER $6.;
format edate date9.;
datalines;
EE1 JOB1 01JAN2013 SUE
EE1 JOB2 01JAN2016 JAN
EE1 JOB3 30JUN2017 JAN
EE1 JOB4 20NOV2017 BOB
EE2 JOB1 30JAN2015 JOHN
EE2 JOB2 30MAY2016 JANET
EE2 JOB3 30JUN2017 DOUG
EE3 JOB1 15MAR2017 DOUG
EE4 JOB1 15OCT2016 JAN
EE4 JOB2 01NOV2017 CURT
;
RUN;
data temp;
merge a(rename=(title=ptitle edate=pdate manager=pmanager))
a(firstobs=2 drop=id);
run;
data want;
set temp;
by id;
if last.id then delete;
run;
Note: code fixed. Sorry for that .
Thank you so much. That work great!
Sorry. I made a mistake. The following should work.
data a;
input @1 ID $3. @5 TITLE $4. @10 EDATE DATE9. @20 MANAGER $6.;
format edate date9.;
datalines;
EE1 JOB1 01JAN2013 SUE
EE1 JOB2 01JAN2016 JAN
EE1 JOB3 30JUN2017 JAN
EE1 JOB4 20NOV2017 BOB
EE2 JOB1 30JAN2015 JOHN
EE2 JOB2 30MAY2016 JANET
EE2 JOB3 30JUN2017 DOUG
EE3 JOB1 15MAR2017 DOUG
EE4 JOB1 15OCT2016 JAN
EE4 JOB2 01NOV2017 CURT
;
RUN;
data temp;
merge a(rename=(title=ptitle edate=pdate manager=pmanager))
a(firstobs=2 drop=id);
run;
data want;
set temp;
by id;
if last.id then delete;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.