BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Gil_
Quartz | Level 8
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;
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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.

PG

View solution in original post

4 REPLIES 4
error_prone
Barite | Level 11
Hardly possible to suggest something without seeing the data you have - at least for me.
PGStats
Opal | Level 21

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.

PG
Gil_
Quartz | Level 8
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
PGStats
Opal | Level 21

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.

PG

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1300 views
  • 0 likes
  • 3 in conversation