topic Re: How to loop through tables in a library in SAS Enterprise Guide

How to loop through tables in a library

ywon111 — Sun, 29 Aug 2021 15:41:26 GMT

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

Re: How to loop through tables in a library

Tom — Sun, 29 Aug 2021 15:49:41 GMT

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?

Re: How to loop through tables in a library

sbxkoenk — Sun, 29 Aug 2021 17:06:56 GMT

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

Re: How to loop through tables in a library

Kurt_Bremser — Sun, 29 Aug 2021 18:52:13 GMT

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.

Re: How to loop through tables in a library

ywon111 — Sun, 29 Aug 2021 20:37:07 GMT

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

Re: How to loop through tables in a library

Kurt_Bremser — Sun, 29 Aug 2021 21:00:57 GMT

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;

Re: How to loop through tables in a library

ywon111 — Sun, 29 Aug 2021 23:21:12 GMT

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;

Re: How to loop through tables in a library

Tom — Sun, 29 Aug 2021 23:31:13 GMT

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;

Re: How to loop through tables in a library

ywon111 — Mon, 30 Aug 2021 00:56:46 GMT

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;

Re: How to loop through tables in a library

Tom — Mon, 30 Aug 2021 01:21:13 GMT

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

Re: How to loop through tables in a library

ywon111 — Mon, 30 Aug 2021 03:50:32 GMT

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

Re: How to loop through tables in a library

Tom — Mon, 30 Aug 2021 04:02:30 GMT

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

Re: How to loop through tables in a library

SASKiwi — Mon, 30 Aug 2021 04:52:26 GMT

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

Re: How to loop through tables in a library

Kurt_Bremser — Mon, 30 Aug 2021 05:20:29 GMT

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