How to get the latest dataset from a library

[kajal_30]

I have lots of datasets in a SAS library but I want to choose the latest one 

for eg library has

abc_2401

abc_2402

abc_2407

abc_2408

efg_2402

efg_2401

hij_2401

hij_2402

how to extract the dataset abc_2408 from the whole list of datasets.

Thanks in advance

Kajal

[PeterClemmensen]

It depends on what you mean by 'latest'? Please be specific.

[kajal_30]

Hi @PeterClemmensen 

As I said in the last line need abc_2408 which is the latest dataset having prefix abc

[ballardw]

---

@kajal_30 wrote:

Hi @PeterClemmensen 

As I said in the last line need abc_2408 which is the latest dataset having prefix abc

---

That is an example, not a rule.

Computer programs need rules to apply in more generic cases.

For example, how do I know you wanted "abc_2408" if there was a "pdq_2408"?

I think you need at least 2 rules, one involving the part before the _ (if any) and if 2408 is supposed to contain some sort of date information tell us how to parse it to determine "latest".

[PaigeMiller]

proc sql noprint;
    select name into :latest from dictionary.tables where libname='YOURLIBNAME' 
    having scan(memname,2,'_')=max(scan(memname,2,'_'));
quit;

This creates a macro variable named &LATEST with the value abc_2408.

PS: Latest is a meaningless word here without additional information. If you have said the value after the underscore is a date in the format YYMM and you want the latest date, that would be a lot more clear. Spending a little extra time (perhaps one minute in this case) to make sure your meaning is clear is always a good thing to do.

[kajal_30]

the code provided didn't work for me getting below in logs: 

NOTE: The query requires remerging summary statistics back with the original data.
NOTE: No rows were selected.

[PaigeMiller]

---

@kajal_30 wrote:

the code provided didn't work for me getting below in logs: 

NOTE: The query requires remerging summary statistics back with the original data.
NOTE: No rows were selected.

---

IMPORTANT RULE: show us your code — don't just say it didn't work. Right now, we don't know what you did, so we can't tell you how to fix it.

[Tom · Accepted solution]

Make sure to use the correct variable (MEMNAME in the this case) into the macro variable.

select memname into :latest trimmed
  from dictionary.tables
  where libname='EG' 
    and memname like 'ABC^_' escape '^'
  having scan(memname,-1,'_')=max(scan(memname,-1,'_'))
;

Note that LIBNAME (and MEMNAME) variable in DICTIONARY.TABLES is always in uppercase.  So assuming the string EG in your original post is the name of the libref that you want find the dataset in then the code should look like above to find the latest ABC_yymm dataset.  If the libref is something else then modify the code to reflect the name that was used for the library.   If you want to find the latest version of some other dataset then change the value used in the WHERE clause.

[kajal_30]

this one worked fine

Thanks 

Kajal