topic Re: Last day service in SAS Programming

Last day service

Gil_ — Tue, 21 Aug 2018 18:21:31 GMT

I am using this code to determine last day service but the out pool put is this
Id. Day. Last day serviced
A1. 05/18/2018. 05/26/2028
B1. 05/17/2018. 05/23/2018

I would need it to look
B1. 05/18/2018. 04/27/2018
I dont need to see date if its after date

proc sql;
create table want as
select a.id,
a.date,
b.date as last_date
from tablea a
left join (select id,
max(date) as date
from tableb
group by id) b
on a.id=b.id;
quit;

Re: Last day service

error_prone — Tue, 21 Aug 2018 19:09:31 GMT

Hardly possible to suggest something without seeing the data you have - at least for me.

Re: Last day service

PGStats — Tue, 21 Aug 2018 19:17:14 GMT

Guessing what you meant from your fuzzy question, I would suggest:

on a.id=b.id

where b.date > a.date;

and replacing the left join with an inner join.

Re: Last day service

Gil_ — Tue, 21 Aug 2018 19:22:41 GMT

I have 2 tables
Services
Id. Date
A1. 05/23/2018
A1. 05/26/2018
A1. 05/30/2018
A1. 05/14/2018
A1. 05/17/2018

Last day service table
Id. Date
A1. 05/10/2018
A1. 05/29/2018
A1. 05/31/2018
Ourput

Id. Date. Last day service
A1. 05/23/2018.
A1. 05/26/2018. 05/25/2018
A1. 05/30/2018. 05/29/2018
A1. 05/14/2018. 05/10/2018
A1. 05/17/2018

Re: Last day service

PGStats — Wed, 22 Aug 2018 02:16:27 GMT

Looking at your example data, there is no way to tell which dates (Services - Last Day Service) to pair up and compare; they all have the same Id.