Assign macro variable value by extracting date from dataset name

[KP12]

Hi Tom, your code works perfectly fine with my case, thank you once again. The query displays the result in descending order of the creation date, but how can I fetch the first record based on on that. I have used monotonic() function like below, as it is not recommended by SAS, can get the same result with some other method ?

%let libref=work;

%let lastdt=none;

proc sql ;

select scan(memname,-1,'_')

into :lastdt

from dictionary.tables

where libname=%upcase("&libref")

and memname like 'ASSISTANCE^_%' ESCAPE '^'

having monotonic()=max(monotonic())

order by memname desc

;

quit;

[RW9]

Something like:

data work.assistance_20140202 work.assistance_20140609;

  set sashelp.class;

run;

proc sql noprint;

  select  distinct(scan(MEMNAME,-1,"_"))

  into    :LASTDT

  from    DICTIONARY.TABLES

  where   LIBNAME="WORK"

    and   index(MEMNAME,"ASSISTANCE")>0

    having   input(scan(MEMNAME,-1,"_"),yymmdd8.)=max(input(scan(MEMNAME,-1,"_"),yymmdd8.));

quit;

%put &LASTDT.;

You could also do:

data tmp;

  set sashelp.vtable (where=(libname="WORK" and index(memname,"ASSIS")>0));

  dt=input(scan(memname,-1,"_"),yymmdd8.);

run;

proc sort data=tmp;

  by dt;

run;

data _null_;

  set tmp (obs=1);

  call symput('LASTDT',put(dt,yymmdd8.));

run;

%put &LASTDT.;

[Tom]

When you select into a macro variable without specifying that it concatenate the results by using the SEPARATED BY clause then PROC SQL will only return the first record.

266  proc sql noprint ;

267  select name into :name trimmed

268    from sashelp.class

269    order by name desc

270    ;

271  %put &=sqlobs &=name ;

SQLOBS=1 NAME=William

272  quit;

[jakarman]

Hi tom, why using the dictionary.tables view as it can be done with the idea of rcwrihgt (data_null- code)?

The difference is: the dictionary.tabels will open and check ALL  tables in All active libraries in your session (how many?) and the list access only those tables really being there.

Although I guess the list access will do a library check (dictionary). You could see that happen when sastrace is on with an external RDBMS.  

[Tom]

Mainly because the request was for information about the metadata and not about the data.  The request was for the name of the dataset.  Note that tricks using INDSNAME will not work for datasets with zero observations.

Dictionary tables SHOULD be smart enough to not open files for libraries that cannot match the where clause.  That is one reason why it is important to use PROC SQL and the DICTIONARY.xxx tables directly rather than useing the views in SASHELP.

[jakarman]

Tom the bad performance behavior of dictionary.tables is documented. SAS os knowing that. It is even worse when having mane pre-assigned RDBMS tables.
Yep it is part of the SQL implementation. SAS(R) 9.4 SQL Procedure User's Guide  The notes are:

Note: SAS does not maintain DICTIONARY table information between queries. Each query of a DICTIONARY table launches a new discovery process.

  When you query DICTIONARY.Tables or Sashelp.Vtable, all of the tables and views in all libraries that are assigned to the SAS session are opened to retrieve the requested information.

  You can use a WHERE clause to help restrict which libraries are searched. However, the WHERE clause does not process most function calls such as UPCASE.

Note: Searching all librefs might cause unexpected results. If all librefs are searched, a view might exist that contains a libref that is not currently assigned to the SAS session. When this view is opened to retrieve information for the query, an error occurs.

Note: If you query table information from a library that is assigned to an external database, and you use the LIBNAME statement PRESERVE_TAB_NAMES=YES option or the PRESERVE_COL_NAMES=YES option, and you
provide the table or column name as it appears in the database, you do not need  to use the UPCASE function.

The problem of retrieving all kind of dictionary info before some action is done is known, see: http://blogs.sas.com/content/sasdummy/2013/07/08/proc-delete-its-not-dead-yet/

Real life experience:

Now do some quality checks on code being delivered by very expensive consultants or analysts as they are complaining performance with SAS is very bad *unhappy users".

Seeing the discovery done several times in one job-flow and discovery being done on not used external tables a response time for some simple SAS steps of 30 minutes.  All those external RDBMS connections with al lot of tables were being new. 

[Tom]

The problems you note are real and suggest that using DICTIONARY tables should be done with care.  For this problem the only possible issue would be if the library the user wanted to search had many tables defined.  The other problems noted do not apply since the use of  where libname='WORK' in the query will prevent SAS from trying to access other libraries.

[jakarman]

Agree on that one with 9.4 and using proc Sql it should be optimized to not access all other libraries. When there is response time problem (extensively researched that one as needing to have total turn=around times below 10 sec)  Seen all those issues with that. AS you cannot trace the discovery process the only measurement is the turn-around time.

This topic is coming at most too late, after image for SAS damage has happened.

How many SAS guys/girls are aware on those quirks behind the curtains of dictionary.tables (members).