How to loop through tables in a library

[ywon111]

How to loop through tables in a library (eg work) and find the table with largest date.

Eg cust1aug2021
Cust2aug2021
Cust3aug2021

I am trying to create a data step that use set cust3aug2021.

Thanks

[Tom]

What is the variable that has the DATE?  What is the type (CHAR or NUM) of the variable?  Does it have a FORMAT attached to it?  What is the format?

Are the structures the same for all three tables?

Or at least is the variable name that you want to use the same in each of them?  Does it have the same type? Same type of content?

[sbxkoenk]

Hello,

You can use the dictionary tables and look for the table with the highest 

• crdate (Date Created) or
• modate (Date Modified)

PROC SQL noprint;
 create table work.tt as
 select * 
 from dictionary.tables
 where libname="WORK"  
; QUIT;

Koen

[Kurt_Bremser]

When putting timestamps in dataset or other filenames, do not (as in NOT) use such a stupid format. Use purely numeric dates in YMD order.

I use "stupid" here because such dates do not sort correctly.

Once you have a usable timestamp in your dataset name like

cust20210801

then you just pick the "highest" name, like

proc sql noprint;
selec max(memname) into :dsname
from dictionary tables
where libname  = "WORK" and memname like 'CUST%';
quit;

With the useless names you currently  have, you need to first read all names, extract the date portion, convert it to a SAS date value, and store that in a variable so you can sort by it.

[ywon111]

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

[Kurt_Bremser]

Try this:

proc 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;

[ywon111]

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;

[Tom]

That code cannot work.  The values of MEMNAME are going to always be in UPPERCASE.

Also what LIBREF did you define to point to where the datasets are?

Let's assume the libref is named MYLIB

proc sql noprint;
create table tables as
  select libname
       , memname
       , input(scan(memname,-1,'_'),?date11.) as date format=date9.
  from dictionary.tables
  where libname='MYLIB'
    and memname like 'CUST^_%' escape '^'
;
%put Found &sqlobs datasets in MYLIB 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;

[ywon111]

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;

[Tom · Accepted solution]

Are you sure the SDLIB libref is defined?  What ENGINE is it using?

The code works fine for me:

1     data CUST_20DEC2020 CUST_17JUL2019 ;
2     run;

NOTE: The data set WORK.CUST_20DEC2020 has 1 observations and 0 variables.
NOTE: The data set WORK.CUST_17JUL2019 has 1 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.03 seconds


3     proc sql noprint;
4     create table tables as
5       select libname
6            , memname
7            , input(scan(memname,-1,'_'),?date11.) as date format=date9.
8       from dictionary.tables
9       where libname='WORK'
10      and memname like 'CUST^_%' escape '^'
11    ;
NOTE: Table WORK.TABLES created, with 2 rows and 3 columns.

12    %put Found &sqlobs datasets in WORK whose names start with CUST_.;
Found 2 datasets in WORK whose names start with CUST_.
13    select catx('.',libname,memname) into :dsname trimmed
14      from tables
15      having date = max(date)
16    ;
NOTE: The query requires remerging summary statistics back with the original data.
17    %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 WORK.CUST_20DEC2020.
18    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.06 seconds
      cpu time            0.03 seconds

[ywon111]

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

[Tom]

Code seems to be working fine. There just aren't any such datasets to find.

[SASKiwi]

Dumb question - did you assign the data library SDLAB before running this? I tried @Tom 's code and it works fine. 

[Kurt_Bremser]

You need to pay attention to details. DICTIONARY tables will always have the libnames and memnames in uppercase for SAS libraries and datasets.