Need to insert rows into work.temp inside of proc cas block

[rgreen33]

I am working on a script that will scan my caslibs and will report to me the datasets that are not loaded.  The code works (email portion lacking).  However, I can only "print" my results.  I need to get them into work.temp so that I can query them for email.  Below is my code.  I have tried everything that I can think of, but I cannot find a way to do an insert inside of my proc cas block.

options emailsys=smtp symbolgen mprint mlogic timezone='america/new_york' options msglevel=i;

/* start cas session */

cas mySession sessopts=(messagelevel=all);

/* assign all caslibs, so that any can be used */

caslib _all_ assign;

proc datasets library=work kill; run; quit;

proc sql;

     create table work.temp (myCaslib char(255), myFilename char(255), myMessage char(255));

quit;

proc cas;

     %let myCaslib = "PUBLIC";

     /* Lists the files in a caslib's data source. */

     table.fileinfo result=fileresult caslib=&myCaslib;

     /* Display the results of the above action; */

     /* Describe(fileresult); */ 

     filelist=findtable(fileresult);

     do j over filelist;

          table.tableexists result=r / name=j.name caslib=&myCaslib;

          if r=0 then print &myCaslib " " (j.name) " : Dataset not loaded.";

          /********************************************************

          *  Need help here.  Instead of the above print, I would like to

          *  write the 3 print items to work.temp.  How can I do this within

          *  this proc cas block?

          *********************************************************/

     end;

quit;

/* Email message with dynamic content based on data */

filename msg email 

 to="myEmail@mydomain.com"

 from="sas-alerts@mydomain.com"

 subject = "SAS Viya Dataset NOT Loaded";

data _null_;

     file msg;

     put "This is a test of email from Viya.";

run;

[SASJedi · Accepted solution]

Try something like this:

proc datasets library=work kill; run; quit;
cas mySession;
proc cas;
     %let myCaslib = PUBLIC;
      /* Lists the files in a caslib's data source. */
     table.fileinfo  result=fileresult / caslib="&myCaslib";
     files=findtable(fileresult);
     saveresult files dataout=work.files; 

     table.tableinfo  result=tableresult / caslib="&myCaslib";
     tables=findtable(tableresult);
     saveresult tables dataout=work.tables; 

quit;
cas mysession terminate;

proc sql;
create table work.notloaded as
select "&myCaslib"  as myCaslib
      ,f.Name as myFileName
      ,catx(" ",f.name,": Dataset not loaded") as myMessage 
   from work.files as f
   where f.Name not in (select SourceName from work.tables);
quit;

[rgreen33]

@SASJedi,

Thank you for the reply. This is great! Now, the next issue is that I want to loop thru all of my CASLIBs...getting a list of all of the files and tables in my CASLIBs. It appears that the method above will not allow for appending to the work.files and work.tables. Any suggestions for this?

Thanks,

Ricky

[SASJedi]

Just use the result set from table.caslibInfo and your original DO OVER technique to create a new work.xxx table for each caslib, then use PROC APPEND to the individual work datasets to a master list.