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
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?
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.
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;
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.
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.