topic Re: SAS Proc Print only if the title is different in SAS Programming

SAS Proc Print only if the title is different

BonnaryW — Tue, 28 Nov 2017 17:30:42 GMT

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

Re: SAS Proc Print only if the title is different

Reeza — Tue, 28 Nov 2017 17:44:10 GMT

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

Re: SAS Proc Print only if the title is different

BonnaryW — Tue, 28 Nov 2017 17:52:58 GMT

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/

Re: SAS Proc Print only if the title is different

Reeza — Tue, 28 Nov 2017 18:04:22 GMT

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.

Re: SAS Proc Print only if the title is different

BonnaryW — Tue, 28 Nov 2017 18:08:43 GMT

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.

Re: SAS Proc Print only if the title is different

Reeza — Tue, 28 Nov 2017 18:09:06 GMT

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;

Re: SAS Proc Print only if the title is different

BonnaryW — Tue, 28 Nov 2017 20:05:43 GMT

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]

Re: SAS Proc Print only if the title is different

Reeza — Tue, 28 Nov 2017 20:16:41 GMT

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

Re: SAS Proc Print only if the title is different

Ksharp — Thu, 30 Nov 2017 13:03:17 GMT

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 .

Re: SAS Proc Print only if the title is different

BonnaryW — Wed, 29 Nov 2017 15:35:41 GMT

Thank you so much. That work great!

Re: SAS Proc Print only if the title is different

Ksharp — Thu, 30 Nov 2017 13:00:59 GMT

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;

Re: SAS Proc Print only if the title is different

BonnaryW — Thu, 30 Nov 2017 15:39:50 GMT

Thank you Ksharp.