Help using Base SAS procedures

max(date) specific for each contract

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

max(date) specific for each contract

Hi guys,

 

I have a question. I want to pull out distinct customer_id and certain (expiration) date from a table that contains history of expiration dates for certain bonuses. 

 

PROC SQL;
create table work.datt as
select distinct t1.CONT_ID, max(t2.EXPIRE_DATE) FORMAT=DATETIME20. from CONTRACTS t1
inner join CONTRACT_HIST t2 on t2.CONT_ID = t1.CONT_ID;
QUIT;

 

But then it takes one, general max date and pulls out contracts only for this one, specific date. Same happens when I'm using function 'having date =max(date)'.

 

Could you please help out how to make it max(date) but separate for each customer?

thanks!


Accepted Solutions
Solution
‎11-25-2016 03:39 AM
Super User
Super User
Posts: 7,401

Re: max(date) specific for each contract

Yes, you haven't supplied any group by statement.  Simplest method is to put your join in a subquery then the returned data group by CONT_ID:

proc sql;
  create table WORK.DATT as
  select  distinct 
          CONT_ID, 
          max(EXPIRE_DATE) format=datetime20. 
  from    (select * 
           from CONTRACTS T1
           inner join CONTRACT_HIST T2 
           on T2.CONT_ID=T1.CONT_ID) 
  group   by CONT_ID;
quit;

Obviously I can't test this as you haven't provided any test data.

View solution in original post


All Replies
Solution
‎11-25-2016 03:39 AM
Super User
Super User
Posts: 7,401

Re: max(date) specific for each contract

Yes, you haven't supplied any group by statement.  Simplest method is to put your join in a subquery then the returned data group by CONT_ID:

proc sql;
  create table WORK.DATT as
  select  distinct 
          CONT_ID, 
          max(EXPIRE_DATE) format=datetime20. 
  from    (select * 
           from CONTRACTS T1
           inner join CONTRACT_HIST T2 
           on T2.CONT_ID=T1.CONT_ID) 
  group   by CONT_ID;
quit;

Obviously I can't test this as you haven't provided any test data.

Occasional Contributor
Posts: 12

Re: max(date) specific for each contract

meaning for example i have:

 

iddate
12016-01-01
12015-01-01
22018-03-01
32013-01-01
32015-01-01
42016-01-01
42017-01-01
42018-10-01

 

 

I want to have:

 

iddate
12016-01-01
22018-03-01
32015-01-01
42018-10-01

 

what I received:

 

iddate
12018-10-01
22018-10-01
32018-10-01
42018-10-01

 

Super User
Super User
Posts: 7,401

Re: max(date) specific for each contract

Yep, did you try my code above?

Occasional Contributor
Posts: 12

Re: max(date) specific for each contract

ok I was dumb for few moments there, group by cont_id solved it. thanks a lot

Smiley Wink

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 195 views
  • 1 like
  • 2 in conversation