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
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?
Hello,
You can use the dictionary tables and look for the table with the highest
PROC SQL noprint;
create table work.tt as
select *
from dictionary.tables
where libname="WORK"
; QUIT;
Koen
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.
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;
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;
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
Code seems to be working fine. There just aren't any such datasets to find.
Dumb question - did you assign the data library SDLAB before running this? I tried @Tom 's code and it works fine.
You need to pay attention to details. DICTIONARY tables will always have the libnames and memnames in uppercase for SAS libraries and datasets.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.