topic Re: Sorting and comparing dates in SAS Programming

Sorting and comparing dates

Fry — Thu, 28 Sep 2017 17:09:02 GMT

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!

Re: Sorting and comparing dates

PaigeMiller — Thu, 28 Sep 2017 17:12:35 GMT

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

Re: Sorting and comparing dates

Astounding — Thu, 28 Sep 2017 17:14:37 GMT

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.)