Sorting and comparing dates

Reply
New User Fry
New User
Posts: 1

Sorting and comparing dates

Hi, I need your help solving this issue:

I have client_id with multiple date of service and I need to place in between the correct range of dates, the date that client make a payment, so for example:

 

Table 1

Client        DateOf Service

m1256          5/2/2013

m1256           7/4/2013

m1256           11/1/2014

m1256           5/11/2015

k5678             3/4/2016

k5678             8/16/2017

r2983            5/6/2015

r2983            12/22/2015

r2983             4/29/2016

 

table2.  Payments

Client         Payment

m1256       5/16/2013

m1256       6/28/2016

k5678        5/14/2016

k5678        7/23/2016

r2983        7/3/2015

r2983        3/1/2016

r2983        5/23/2016

 

I think I can rank the dates in table 1 and stablish start and end date, maybe?   Any help will be awesome!

Respected Advisor
Posts: 2,175

Re: Sorting and comparing dates

You could just append the two data sets and then sort by client and date.

--
Paige Miller
Super User
Posts: 6,011

Re: Sorting and comparing dates

All your dates need to be actual SAS dates, not text strings.

 

And your data sets need to be in sorted order.

 

With those conditions in place, this would be one way to rearrange the pieces:

 

data want;

set table1 (rename=(DateofService=Date) in=in1)

   table2 (rename=(Payment=Date) in=in2);

by Client Date;

if in1 then Action='Service';

else Action='Payment';

run;

 

Try it, see if you like what you get.  (If you don't like it, you might have to design what you would like the solution to look like before programming can begin.)

Ask a Question
Discussion stats
  • 2 replies
  • 79 views
  • 2 likes
  • 3 in conversation