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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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 .

View solution in original post

11 REPLIES 11
Reeza
Super User

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

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/

Reeza
Super User

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. 

BonnaryW
Obsidian | Level 7
Yes, the title is the variable "Title". It didn't change because that is latest record and there's no records before and after it.
Thank you again.
Reeza
Super User

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;

BonnaryW
Obsidian | Level 7
Hi Reeza,
I can't get it to work, this what I have:
data a;
input @1 ID $3. @5 TITLE $4. @10 EDATE DATE9. @20 MANAGER $6.;
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;
proc sort; by id edate;

data want;
set a;

by id edate;

prev_title= lag(title);
prev_date = lag(Edate);
prev_manager = lag(manager);

FORMAT PREV_DATE EDATE mmddyy10.;

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;
============================
This is the result:
[cid:image001.png@01D36841.3184B520]

Reeza
Super User

There’s no attachment or image if thats what you were trying to do. 

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

Thank you so much. That work great!

Ksharp
Super User

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;
BonnaryW
Obsidian | Level 7
Thank you Ksharp.

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
  • 11 replies
  • 1029 views
  • 0 likes
  • 3 in conversation