Find a table in a library based on specific parameters

[nicnad]

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

[PGStats]

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

[nicnad]

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]

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]

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 · Accepted solution]

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

[nicnad]

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;