BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pkonopnicki
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

pkonopnicki
Obsidian | Level 7

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yep, did you try my code above?

pkonopnicki
Obsidian | Level 7

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

😉

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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