BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rhapsody
Calcite | Level 5

This is what I have

 

libname tables "c:\Data";

 

In the libname "tables" I have many tables looking like this (this is just part of all the tables, there are at least a 100 more with the same name format)

 

result_201103_credit

result_201106_credit

result_201109_credit

result_201112_credit

result_201903_credit

 

I want to write a sas code that extracts the latest date and turns it into a macro variable. So when I write

 

%put &date.;

 

it returns

 

201903

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ErikLund_Jensen
Rhodochrosite | Level 12

Hi @rhapsody 

 

You could write a macro to give the wanted result. The code is based on the good advices you have got so far:

 

%macro getlasttable;
	%if not %symexist(date) %then %global date;
	%let date =;
	proc sql noprint;
			select max(scan(memname,2,'_')) into :date
			from dictionary.tables
			where 
				libname = upcase("tables")
				and memname like 'RESULT%CREDIT';
	quit;
%mend;

%getlasttable;
%put &=date;

201903

 

View solution in original post

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

Consider that you have the library tables, then you can try the below code

 

proc sql;
create table test as select memname from dictionary.tables where libname='TABLES';
quit;


proc sql;
select max(input(scan(memname,2,'_'),best.)) as memname2 into: date from table ;
quit;

%put &date;
Thanks,
Jag
yabwon
Amethyst | Level 16

Hi,

 

try something like this:

 

/* example data */
data 
  result_201103_credit
  result_201106_credit
  result_201109_credit
  result_201112_credit
  result_201903_credit
;
x = 42;
run;

libname tables (work);

/* get the dataset names */
proc contents 
  data = tables._all_
  memtype = DATA
  OUT=SAS_data_set(keep = memname where=(memname like 'RESULT%CREDIT')) /* names are "upcassed" */
;
run;

/* keep only digits in names and get the max value */
proc sql;
  select 
    max(compress(memname, , "KD"))
  into 
    :date trimmed
  from 
    SAS_data_set
  ;
quit;

%put *&date.*;

 

all the best

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



ErikLund_Jensen
Rhodochrosite | Level 12

Hi @rhapsody 

 

You could write a macro to give the wanted result. The code is based on the good advices you have got so far:

 

%macro getlasttable;
	%if not %symexist(date) %then %global date;
	%let date =;
	proc sql noprint;
			select max(scan(memname,2,'_')) into :date
			from dictionary.tables
			where 
				libname = upcase("tables")
				and memname like 'RESULT%CREDIT';
	quit;
%mend;

%getlasttable;
%put &=date;

201903

 

dario_medina
Fluorite | Level 6
There is a paper that you might find useful : Automate in a Dash with SAS: Time saving techniques for building quality improvement dashboards. Starts by capturing all file names from a folder and getting the latest named file.
Hope it helps

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 3230 views
  • 0 likes
  • 5 in conversation