SAS Proc Print only if the title is different

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

SAS Proc Print only if the title is different

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


Accepted Solutions
Solution
‎11-29-2017 10:31 AM
Super User
Posts: 10,679

Re: SAS Proc Print only if the title is different

[ Edited ]
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


All Replies
Super User
Posts: 23,224

Re: SAS Proc Print only if the title is different


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;
Contributor
Posts: 47

Re: SAS Proc Print only if the title is different

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/

Super User
Posts: 23,224

Re: SAS Proc Print only if the title is different

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. 

Contributor
Posts: 47

Re: SAS Proc Print only if the title is different

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.
Super User
Posts: 23,224

Re: SAS Proc Print only if the title is different

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;

Contributor
Posts: 47

Re: SAS Proc Print only if the title is different

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]

Super User
Posts: 23,224

Re: SAS Proc Print only if the title is different

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

Solution
‎11-29-2017 10:31 AM
Super User
Posts: 10,679

Re: SAS Proc Print only if the title is different

[ Edited ]
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 .

Contributor
Posts: 47

Re: SAS Proc Print only if the title is different

Thank you so much. That work great!

Super User
Posts: 10,679

Re: SAS Proc Print only if the title is different

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;
Contributor
Posts: 47

Re: SAS Proc Print only if the title is different

Thank you Ksharp.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 210 views
  • 0 likes
  • 3 in conversation