BookmarkSubscribeRSS Feed
Question
Fluorite | Level 6

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

 

 

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

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?

Question
Fluorite | Level 6
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. -##
ballardw
Super User

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

thomp7050
Pyrite | Level 9

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;

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 804 views
  • 0 likes
  • 4 in conversation