27 proc sql noprint; 28 create table tables as 29 select libname 30 , memname 31 , input(scan(memname,-1,'_'),?date11.) as date format=date9. 32 from dictionary.tables 33 where libname='SDLAB' 34 and memname like 'CUST^_%' escape '^' 35 ; NOTE: Table WORK.TABLES created, with 0 rows and 3 columns. 36 %put Found &sqlobs datasets in SDLAB whose names start with CUST_.; Found 0 datasets in SDLAB whose names start with CUST_. 37 select catx('.',libname,memname) into :dsname trimmed 38 from tables 39 having date = max(date) 40 ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: No rows were selected. 41 %put The dataset with the largest date on the end of its name is &dsname..; The dataset with the largest date on the end of its name is CUST . 42 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.02 seconds ... View more

Thanks Tom. The tables are like below. The library name is SDLAB. CUST_20DEC2020 CUST_17JUL2019 Have tried code below, but no luck 😞 proc sql noprint; create table tables as select libname , memname , input(scan(memname,-1,'_'),?date11.) as date format=date9. from dictionary.tables where libname='SDLAB' and memname like 'CUST^_%' escape '^' ; %put Found &sqlobs datasets in SDLAB whose names start with CUST_.; select catx('.',libname,memname) into :dsname trimmed from tables having date = max(date) ; %put The dataset with the largest date on the end of its name is &dsname..; quit; ... View more

Sorry to keep coming back to this one. Have given the code a run but all dates are missing. The library these cust tables are in a library (e.g. abcd), do I need to code this in? memname date cust . cust . cust . cust_02AUG2021 . cust_04AUG2020 . cust_04JUL2019 . cust_05FEB2020 . cust_05MAR2021 . Code roc sql noprint; create table tables as select memname, input(scan(memname,2,'_'),date9.) as date from dictionary.tables where memname like 'cust%'; select memname into :dsname from tables having date = max(date); quit; ... View more

Sorry, have relooked this again and the tables are currently named as such. Have tried to used the suggested codes but not much luck. Perhaps the naming is not good enough? cust_04JUL2019 cust_05FEB2020 etc ... View more

Sorry, slight changes, this is what I want id apple orange 1 1 1 2 1   3 1     Thanks  ... View more

Have used the Substring operator as below but because it's a character the sorting doesn't quite work correctly as dates - Substring('Start date'n, 1, 10) Sorry, but I am referring to SAS VA not EG, is there a way to fix in VA? Thanks ... View more

Is there a way to add into the codes to exclude certain dates? E.g. don't count if date = 1/04/21 datalines; 123 1/04/2021 0 123 5/04/2021 0 123 9/04/2021 1 123 30/04/2021 0 ... View more

Thanks for the solution. Is it possible to get working days <7 ? ... View more