topic Re: Retrieving records with the latest date by customer_id, week_no in SAS Programming

Retrieving records with the latest date by customer_id, week_no

Question — Mon, 24 Apr 2017 13:50:24 GMT

Hi all,

I have a table with customer_id, trans_date , week_no and another variablle number wich takes different values each date. I would like to retrive the most recent ' Number' by customer)id and week_no. It sounds easy, but the program below doesn't work.

proc sort data=work.datasets ;
by customer_id week_no descending trans_date number ;
run;

data work.latest_record;
set work.datasets ;
by customer_id week_no descending trans_date number;
if first.customer_id and first.week_no and first.trans_date and first.number then output ;
run;

I have attached a file which contains what I have, and what I want.

Your help would be much appreciated.

Many Thanks

Re: Retrieving records with the latest date by customer_id, week_no

RW9 — Mon, 24 Apr 2017 13:53:14 GMT

Post test data in the form of a datastep!

Downloading files is not secure. As such I would point out that:

data work.latest_record;
set work.datasets ;
by customer_id week_no descending trans_date number;
if first.number then output ;
run;

Would seem to be the same as what you posted, first and last gets assigned within each subgroup. As for "but the program below doesn't work." what does not work, does it fail to run, log errors? It doesn't give the right result - assess the sort?

Re: Retrieving records with the latest date by customer_id, week_no

Question — Mon, 24 Apr 2017 14:30:09 GMT

Hi RW9,

Thank you for your email.
I've been told to attach files going forward. The details of the file are
below.

It doesn't work because it only gives the first row per customer, please
see below results from the code table.

Thank You

*Have*

*customer_id*

*trans_date*

*week_start_date*

*week_end_date*

*WEEK_NO*

*no_trans*

*Number*

50026

03-Jan-17

01-Jan-17

07-Jan-17

1

1

30,000

50026

03-Jan-17

01-Jan-17

07-Jan-17

1

.

21,000

50026

04-Jan-17

01-Jan-17

07-Jan-17

1

.

2,000

50026

06-Jan-17

01-Jan-17

07-Jan-17

1

1

31,000

50026

07-Jan-17

01-Jan-17

07-Jan-17

1

2

5,500

50026

10-Jan-17

08-Jan-17

14-Jan-17

2

2

40,000

50026

11-Jan-17

08-Jan-17

14-Jan-17

2

.

7,300

50026

13-Jan-17

08-Jan-17

14-Jan-17

2

1

53,000

50026

14-Jan-17

08-Jan-17

14-Jan-17

2

.

10,400

50027

03-Jan-17

01-Jan-17

07-Jan-17

1

1

60,000

50027

03-Jan-17

01-Jan-17

07-Jan-17

1

.

42,000

50027

04-Jan-17

01-Jan-17

07-Jan-17

1

.

4,000

50027

06-Jan-17

01-Jan-17

07-Jan-17

1

2

62,000

50027

07-Jan-17

01-Jan-17

07-Jan-17

1

3

11,000

50027

10-Jan-17

08-Jan-17

14-Jan-17

2

2

80,000

50027

11-Jan-17

08-Jan-17

14-Jan-17

2

.

14,600

50027

13-Jan-17

08-Jan-17

14-Jan-17

2

3

106,000

50027

14-Jan-17

08-Jan-17

14-Jan-17

2

.

20,800

*Want*

*customer_id*

*trans_date*

*week_start_date*

*week_end_date*

*WEEK_NO*

*no_trans*

*Number*

50026

07-Jan-17

01-Jan-17

07-Jan-17

1

2

5,500

50026

14-Jan-17

08-Jan-17

14-Jan-17

2

.

10,400

50027

07-Jan-17

01-Jan-17

07-Jan-17

1

3

11,000

50027

14-Jan-17

08-Jan-17

14-Jan-17

2

.

20,800

*Results from the code*

*customer_id*

*trans_date*

*week_start_date*

*week_end_date*

*WEEK_NO*

*no_trans*

*Number*

50026

07-Jan-17

01-Jan-17

07-Jan-17

1

2

5,500

50027

07-Jan-17

01-Jan-17

07-Jan-17

1

3

11,000

##- Please type your reply above this line. Simple formatting, no
attachments. -##

Re: Retrieving records with the latest date by customer_id, week_no

thomp7050 — Mon, 24 Apr 2017 14:42:32 GMT

I typically recommend proc sql, which may be copied and pasted into several types of software (e.g. R, SPSS, etc.)

PROC SQL; 
SELECT  customer_id, trans_date, week_no, number
FROM ( customer_id, MIN(trans_date) AS MINTRANS
FROM MYSET
GROUP BY CUSTOMER_ID) P
LEFT JOIN MYSET ON MYSET.CUSTOMER_ID = P.CUSTOMER_ID
AND MYSET.TRANS_DATE = P.MINTRANS
QUIT;

Re: Retrieving records with the latest date by customer_id, week_no

ballardw — Mon, 24 Apr 2017 15:35:22 GMT

Please examine:

data work.example;
   informat id $5. week_no best5. trans_date mmddyy10. number best5.;
   format trans_date mmddyy10.;
   input id week_no trans_date number;
datalines;
1  1  01/02/2017 25
1  1  01/03/2017 26
1  1  01/04/2017 27
1  2  01/09/2017 35
1  2  01/10/2017 36
1  2  01/11/2017 37
2  1  01/02/2017 45
2  1  01/03/2017 46
2  1  01/04/2017 47
2  2  01/09/2017 55
2  2  01/10/2017 56
2  2  01/11/2017 57
;
run;

proc sort data=work.example ;
  by id week_no descending trans_date number ;
run;

data work.latest_record;
  set work.example ;
  by id week_no ;
  if first.week_no  then output ;
run;

The data step is the preferred way to show data as we can run the code and generate a data set. If you have an existing data set then Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You only got the first record for each id because that is what you specified: If first.customer_id.