Check existence of CAS library from V9

[ErikLund_Jensen]

Hi Experts

Vi are using SAS9 to load tables in CAS, so we have BASE libraries and CAS libraries defines in SAS9. A given Base Librariey correspond to a given CAS librariy, and I have a SAS9 job with the purpose of finding mismatch between the corresponding libraries.

The technique is simple. A loop over all libraries, where a member list from Proc Content on the V9 library is compared to a member list from Proc Content on the CAS library. This works perfectly well as long as the CAS library is defined in CAS also, so the CAS library in V9 has a target, but it fails otherwise. The CAS libname allocation works in all cases (librc=0), but it fails in the Proc Content step. So I try to work around the problem by checking if the library exists in CAS, so I can bypass the comparison when there is nothing to compare.

I tried the following code:

options cashost="dsasva01.odknet.dk" casport=5570;

proc cas;
    table.queryCaslib /
       caslib="VIYA_BUF_BACKEND";
quit;

proc cas;
   table.queryCaslib /
      caslib="VIYA_BUF_TVAERGAAENDE";
quit;

NOTE: Active Session now CASAUTO.
{VIYA_BUF_BACKEND=FALSE}
NOTE: PROCEDURE CAS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: Active Session now CASAUTO.
{VIYA_BUF_TVAERGAAENDE=TRUE}
NOTE: PROCEDURE CAS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

It works as expected, but I cannot use the information, because it is written to the log only, and I don't know how to capture it in my V9 program. In theory, one could redirect the log from each CAS procedure call to another new logfile, and read it with a Data Step to get the information, but this would clutter my rather complicated code to a point where it became almost unmaintainable, so I hope for a smarter solution. My CAS knowledge is very limited, so I have absolutely no ideas, and I would be happy for any suggestions.

Here is the working code that fails if the V9 CAS library doesn't exist in CAS, so I must find some way to implement the check:

/***********************************************************************************/
/* caslibcontent                                                    erlu 3.12.2022 */
/*                                                                                 */
/* Danner indhold af CAS libraries specificeret i et inputdatasæt.                 */
/*                                                                                 */
/* Input skal som minumum indeholde kolonnerne                                     */
/*     Metaserver                                                                  */
/*     Libref                                                                      */
/*     CASLibname                                                                  */
/*     DBServer                                                                    */
/* Input skal være sorteret på Metaserver.                                         */
/*                                                                                 */
/*                                                                                 */
/***********************************************************************************/

* Initier outputdatasæt;
data &_OUTPUT;
  length 
    LIBNAME $8 
    MEMNAME $32
    NAME $32
    TYPE 8
    LENGTH 8 
    VARNUM 8
    LABEL $256
    FORMAT $32
    FORMATL 8
    FORMATD 8
    NOBS 8
    SASLibraryID $17
    CASLibname $60
    DBServer $30
    MetaServer $30
  ;
  stop;
run;

* håndter ikke-V7-navne i CAS;
options validmemname=EXTEND validvarname=ANY;

* Loop over proc-contents for alle libnames og saml tabelinformation fra CAS;
data _null_;
  set &_INPUT(where=(CASLibname ne 'VIYA_BUF_BACKEND')); /* Where SKAL slettes */
  by MetaServer;
  retain current_session;

  * Initier ny CAS session, når vi starter på et nyt miljø;
  if first.Metaserver then do;
    call execute(catt('options cashost="', dbserver, '" casport=5570;'));
    current_session = cats("&sysuserid", put((datetime()*1000),13.0),put(_N_,8.));
    put current_session=;
    call execute(catx(' ', 'cas', current_session, ';'));
  end;

  * Slet workdata inden behandling af nyt libname;
  call execute('proc datasets lib=work nolist; delete tmp tmp2; run;');

  * Alloker libname;
  libref = substr(dbserver,1,1) || put(_N_,Z7.);
  call execute(
    catx(' ',
      'libname', 
      Libref, 
      catt('CAS CASLIB="', CASLibname, '"'),
      catt('SERVER="', DBserver, '"' ), 
      'PORT=5570;'
    )
  );
  
  * Kør proc Contents på libname;
  call execute(
    catt(
      'proc contents data=',
      Libref,
      '._all_ noprint out=tmp; run;'
    )
  );
  
  * Frigiv libname igen;
  call execute(catx(' ',' libname', Libref, 'clear;'));

  * Udtræk relevante content-oplysninger;
  call execute(
    catx(' ', 
        'proc sql; create table tmp2 as select',
        'LIBNAME length=8,',
        'MEMNAME length=32,',
        'NAME length=32,',
        'TYPE length=8,',
        'LENGTH length=8,',
        'VARNUM length=8,',
        'LABEL length=256,',
        'FORMAT length=32,',
        'FORMATL length=8,',
        'FORMATD length=8,',
        'NOBS length=8,',
        catt('"', ID, '"'), 'as SASLibraryID length=17,', 
        catt('"', CASLibname, '"'), 'as CASLibname length=60,', 	 
        catt('"', dbserver, '"'), 'as DBServer length=30,',
        catt('"', MetaServer, '"'), 'as MetaServer length=30',
      'from tmp; quit;'
    )
  );

  * Append udtræk til outputfil;
  call execute(
    catx(' ',
      'proc append base=&_OUTPUT data=tmp2 (keep=',
      'LIBNAME MEMNAME NAME TYPE LENGTH VARNUM LABEL FORMAT FORMATL FORMATD NOBS SASLibraryID CASLibname DBServer MetaServer',
      ') force; run;'
    )
  );

  * Afslut igangværende CAS session, når vi er igennem et miljø;
  if last.metaserver then do;
    call execute(catx(' ', 'cas', current_session, 'terminate;'));
  end;
run;

* Resæt til DWH standard;
options validmemname=COMPATIBLE validvarname=V7;

[ErikLund_Jensen]

Addition
My post is not very precise. The comparison is done afterwards. In the code above, I build the list of CAS libnames/members, and want to bypass the code from Proc Content to (including) Append, if the library is not defined in CAS.

[s_lassen · Accepted solution]

I am not that familiar with CAS, but I see two possibilities.

Assuming that the PROC CONTENTS fails with an error, you can use the SYSCC variable to test if it went well. In order to do that, I would suggest that you put the code that you want executed into a macro, e.g.:

%macro cas_contents(libref,ID,CASLibname,dbserver,MetaServer,_OUTPUT); 
  * Kør proc Contents på libname;
  %let syscc=0; 
  proc contents data=&Libref.._all_ noprint out=tmp; run;
  * Frigiv libname igen;
  libname &Libref clear;
  
  %if &syscc<=4 %then %do; /* if PROC CONTENTS failed, library does not exist in CAS, do nothing */
     * Udtræk relevante content-oplysninger;
     proc sql; 
	   create table tmp2 as select
        LIBNAME length=8,
        MEMNAME length=32,
        NAME length=32,
        TYPE length=8,
        LENGTH length=8,
        VARNUM length=8,
        LABEL length=256,
        FORMAT length=32,
        FORMATL length=8,
        FORMATD length=8,
        NOBS length=8,
        "&ID" as SASLibraryID length=17,
        "&CASLibname" as CASLibname length=60, 	 
        "&dbserver" as DBServer length=30,
        "&MetaServer" as MetaServer length=30
       from tmp; 
	 quit;

     * Append udtræk til outputfil;
      proc append base=&_OUTPUT 
	     data=tmp2 (keep=LIBNAME MEMNAME NAME TYPE LENGTH VARNUM LABEL FORMAT FORMATL FORMATD NOBS SASLibraryID CASLibname DBServer MetaServer)
         force; 
	 run;
  %end;
%mend;

And then replace the multiple executes with a call of the macro (just showing the single data step):

* Loop over proc-contents for alle libnames og saml tabelinformation fra CAS;
data _null_;
  set &_INPUT(where=(CASLibname ne 'VIYA_BUF_BACKEND')); /* Where SKAL slettes */
  by MetaServer;
  retain current_session;

  * Initier ny CAS session, når vi starter på et nyt miljø;
  if first.Metaserver then do;
    call execute(catt('options cashost="', dbserver, '" casport=5570;'));
    current_session = cats("&sysuserid", put((datetime()*1000),13.0),put(_N_,8.));
    put current_session=;
    call execute(catx(' ', 'cas', current_session, ';'));
  end;

  * Slet workdata inden behandling af nyt libname;
  call execute('proc datasets lib=work nolist; delete tmp tmp2; run;');

  * Alloker libname;
  libref = substr(dbserver,1,1) || put(_N_,Z7.);
  call execute(
    catx(' ',
      'libname', 
      Libref, 
      catt('CAS CASLIB="', CASLibname, '"'),
      catt('SERVER="', DBserver, '"' ), 
      'PORT=5570;'
    )
  );
  
  * Insert contents if library exists in CAS
  call execute(
    '%cas_contents('||
    catx(',',libref,ID,CASLibname,dbserver,MetaServer,"&_OUTPUT")||
	');';
 
  * Afslut igangværende CAS session, når vi er igennem et miljø;
  if last.metaserver then do;
    call execute(catx(' ', 'cas', current_session, 'terminate;'));
  end;
run;

Another possibility is to use the SAVERESULT statement in PROC CAS, to get the results of the table.queryCaslib statement. There is an example here.

[ErikLund_Jensen]

@s_lassen 

Thank you very much for your suggestions and the work you put into rewriting my code. It looks promising, but I haven't found time to test it yet because of other more urgent work. I will find time tomorrow and return.

My first try will be the simple solution - let it fail and capture the error. I am not sure if a Proc Content error will force SAS into Syntaxcheck mode, but that could be tested and easily remediated. 

This solution violates  a rule we have about not tolerating things that causes red lines in the log without also forcing the job to fail, but we might live with that. I like your other suggestion better, though. But as I wrote, I know nothing about CAS, and I don't know if the saveresult method in a proc CAS call will return a CAS table or a V9 table, so I am not sure if I can find out how to implement it. 

[s_lassen]

So I assume that you normally run with the ERRORABEND option on, which causes the job to fail when encountering an error. I think you can just use OPTIONS NOERRORABEND before PROC CONTENTS, and then set it back afterwards.

If you want more than that, you can also check if the error from PROC CONTENTS means that the table/library in question does not exist (use the %SYSRC macro, so that it will work on all platforms and in future versions), or something else, in which case you can execute an %ABORT ABEND statement in the macro.

[ErikLund_Jensen]

Hi @s_lassen 

Thank you, I will try it out later. 

Until tomorrow

Erik

[ErikLund_Jensen]

Hi @s_lassen 

I finally managed to get it to work. Actually, I didn't use your code, but found another way. However, your code and comments got me thinking and led me on the right track, so it deserves an "Accepted Solution". 

I could not find a way to check for success if the libname allocation. The libname statement writes a note only, if the fhysical library doesn't exist, but nothing else. I tried to let the Proc Contents fail and reset error conditions, but that couldn't fool our Log Scanner, and I didn't want the job to fail, so I ended up with redirecting the log and retrieve the "not exist" note from that.

I couldn't get the macro call working either, so I dropped the macro and wrote the code "inline". It turned out that not only the macro call, but all macro statements excute immediately when the code is executed by Call Execute, unless they are put in a %nrstr function. After discovering that, I could have made a macro instead, but it works as is, and I had other pressing matters to see to. 

/***********************************************************************************/
/* caslibcontent                                                    erlu 3.12.2022 */
/*                                                                                 */
/* Danner indhold af CAS libraries specificeret i et inputdatasæt.                 */
/*                                                                                 */
/* Input skal som minumum indeholde kolonnerne                                     */
/*     Metaserver                                                                  */
/*     Libref                                                                      */
/*     CASLibname                                                                  */
/*     DBServer                                                                    */
/* Input skal være sorteret på Metaserver.                                         */
/*                                                                                 */
/*                                                                                 */
/***********************************************************************************/

* Initier outputdatasæt;
data &_OUTPUT;
  length 
    LIBNAME $8 
    MEMNAME $32
    NAME $32
    TYPE 8
    LENGTH 8 
    VARNUM 8
    LABEL $256
    FORMAT $32
    FORMATL 8
    FORMATD 8
    NOBS 8
    SASLibraryID $17
    CASLibname $60
    DBServer $30
    MetaServer $30
  ;
  stop;
run;

/*---------------------------------------------------------------------------------*/
/* Hovedprogram til at gennemløbe input med CAS libnames og danne output med Proc  */
/* Content resultater fra alle libnames                                            */
/*---------------------------------------------------------------------------------*/

* håndter ikke-V7-navne i CAS;
options validmemname=EXTEND validvarname=ANY;

* Loop over proc-contents for alle libnames og saml tabelinformation fra CAS;
data _null_;
  set &_INPUT;
  by MetaServer;
  retain current_session;
  
  filename liblog temp;

  * Initier ny CAS session, når vi starter på et nyt miljø;
  if first.Metaserver then do;
    call execute(catt('options cashost="', dbserver, '" casport=5570;'));
    current_session = cats("&sysuserid", put((datetime()*1000),13.0),put(_N_,8.));
    put current_session=;
    call execute(catx(' ', 'cas', current_session, ';'));
  end;

  * Slet workdata inden behandling af nyt libname;
  call execute('proc datasets lib=work nolist; delete tmp tmp2; run;');

  * Rediriger log;
  call execute('proc printto log=liblog new; run;');

  * Alloker libname;
  libref = substr(dbserver,1,1) || put(_N_,Z7.);
  call execute(
    catx(' ',
      'libname', 
      Libref, 
      catt('CAS CASLIB="', CASLibname, '"'),
      catt('SERVER="', DBserver, '"' ), 
      'PORT=5570;'
    )
  );

  %* Tag log tilbage;
  call execute('proc printto; run;');
 
  %* Indlæs templog og sæt flag for om library findes;
  call execute(
    catx(' ',
      'data _null_;',
        'infile liblog end=eof;',
        'retain result 0;',
        'input;',
        'put _infile_;',
        'result = max(result,index(_infile_,"does not exist"));',
        'if eof then do;', 
          'put result=;',
          'call symputx("result",result);',
        'end;',
      'run;'
    )
  );

  * TEST på result;
  call execute(
    catx(' ',
      '%nrstr(%%if) %nrstr(&result) = 0 %nrstr(%%then) %nrstr(%%do);',
        '%nrstr(%%put) **** Libname OK;',

        catt('proc contents data=', Libref, '._all_ noprint out=tmp; run;'),

        'proc sql; create table tmp2 as select',
          'LIBNAME length=8,',
          'MEMNAME length=32,',
          'NAME length=32,',
          'TYPE length=8,',
          'LENGTH length=8,',
          'VARNUM length=8,',
          'LABEL length=256,',
          'FORMAT length=32,',
          'FORMATL length=8,',
          'FORMATD length=8,',
          'NOBS length=8,',
          catt('"', ID, '"'), 'as SASLibraryID length=17,', 
          catt('"', CASLibname, '"'), 'as CASLibname length=60,', 	 
          catt('"', dbserver, '"'), 'as DBServer length=30,',
          catt('"', MetaServer, '"'), 'as MetaServer length=30',
        'from tmp; quit;',

        'proc append base=&_OUTPUT data=tmp2 (keep=',
         'LIBNAME MEMNAME NAME TYPE LENGTH VARNUM LABEL FORMAT FORMATL FORMATD NOBS SASLibraryID CASLibname DBServer MetaServer',
        ') force; run;',

      '%nrstr(%%end);', 
      '%nrstr(%%else) %nrstr(%%do);', 
      '%nrstr(%%put) **** Libname findes ikke; %nrstr(%%end);'
    )
  );

  %* Frigiv libname igen;
  call execute(catx(' ',' libname', Libref, 'clear;'));

  * Afslut igangværende CAS session, når vi er igennem et miljø;
  if last.metaserver then do;
    call execute(catx(' ', 'cas', current_session, 'terminate;'));
  end;
run;

* Resæt til DWH standard;
options validmemname=COMPATIBLE validvarname=V7;