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

Hi,

I have a library named FTM.

The tables in this library goes like this :

FTM201102

FTM201304

FTM201305T

FTM201306

FTM201306T

FTM201307

I would like to have a sas or sql query that looks in that library and find the latest table based on those parameters :

The six digits after FTM are always : YYYYMM

The table name can either finish with a T or not

I would like to write my query so that it could return the latest table ending with or without a T and put the result in a variable.

Can you please help me write that query?

Thank you for your help and time

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

Actually, the SUBSTR part is not necessary, this should do the trick :

proc sql noprint;
select max(memname)
into :lastMember
from dictionary.tables
where libname="FTM" and memname like "FTM%";
quit;

%put &lastMember;

You could also use pattern matching, but that might be overkill.

PG

PG

View solution in original post

6 REPLIES 6
PGStats
Opal | Level 21

Untested

proc sql noprint;

select max(substr(memname,1,9))

into :lastMember

from dictionary.tables

where libname="FTM";

quit;

%put &lastMember;

The table name that you want should be in macro variable lastMember.

PG

PG
nicnad
Fluorite | Level 6

Thank you very much for the quick reply.

max(substr(memname,1,9)) only returns the last table based on alphabetical order.

Within my library, I have a table named tmp_ftm2010 and it returned tmp_ftm20.

But I think we are getting closer to the solution with the use of dictionary.tables where libname = "FTM".

Would it be possible to do something like (VBA like) in SAS or SQL :

mydate = 0

For each tables in  libname="FTM"

if substr(memname,1,3) = "FTM" then

if right(memname,1) = "T" then

if substr(memname,4,6) > mydate then mydate = substr(memname,4,6)

end if

end if

Next tables

mytablename = "FTM" & mydate & "T"

Hope you understand what I am trying to do.

Thank you for your help and time

esjackso
Quartz | Level 8

Did you try adding to where condition:

and memname=: "FTM"

which should limit to only tables that start with FTM. Of course if their are tables with and without a T with the same date I think it would only return the without T version.

EJ

Reeza
Super User

The code doesn't work, because you didn't provide all the information. Add a filter to the sql step to only get tables that start with FTM first, then try it again.

This method is faster than what you're suggesting.

PGStats
Opal | Level 21

Actually, the SUBSTR part is not necessary, this should do the trick :

proc sql noprint;
select max(memname)
into :lastMember
from dictionary.tables
where libname="FTM" and memname like "FTM%";
quit;

%put &lastMember;

You could also use pattern matching, but that might be overkill.

PG

PG
nicnad
Fluorite | Level 6

Thank you very much for your help.

For my specific need, this works like a charm :

proc sql noprint;

select max(memname)

into :lastMember

from dictionary.tables

where libname="FTM" and memname like "FTM201%" and substr(memname,length(memname)-1,1) = "T";

quit;

%put &lastMember;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1007 views
  • 3 likes
  • 4 in conversation