topic Re: How to get the latest dataset from a library in SAS Programming

How to get the latest dataset from a library

kajal_30 — Wed, 18 Sep 2024 17:33:35 GMT

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

Re: How to get the latest dataset from a library

PeterClemmensen — Wed, 18 Sep 2024 17:37:12 GMT

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

Re: How to get the latest dataset from a library

kajal_30 — Wed, 18 Sep 2024 17:40:19 GMT

Hi @PeterClemmensen

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

Re: How to get the latest dataset from a library

PaigeMiller — Wed, 18 Sep 2024 17:48:03 GMT

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.

Re: How to get the latest dataset from a library

kajal_30 — Wed, 18 Sep 2024 17:58:57 GMT

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.

Re: How to get the latest dataset from a library

Tom — Wed, 18 Sep 2024 18:16:10 GMT

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.

Re: How to get the latest dataset from a library

PaigeMiller — Wed, 18 Sep 2024 18:20:21 GMT

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

Re: How to get the latest dataset from a library

kajal_30 — Wed, 18 Sep 2024 18:59:07 GMT

this one worked fine

Thanks

Kajal

Re: How to get the latest dataset from a library

ballardw — Wed, 18 Sep 2024 19:01:48 GMT

@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".