How to efficiently delete tables from a library

[Aexor]

I have table pgpr_rowcnt which will store 100000s of coloumns like this. these are present in sasme library

relname           rowcount

k__pgpr_3343_1341 0

k__pgpr_3343_1352 0

k__pgpr_3343_1537 0

k__pgpr_3513_1341 4

k__pgpr_3513_1352 5

k__pgpr_3513_1537 2

k__pgpr_3232_1537 2

lcp_pgpr_3232_1352 13300

lcp_pgpr_3232_1341 10640

lcp_pgpr_3343_1341 372

lcp_pgpr_3121_1352 19220

lcp_pgpr_3121_1537 7688

lcp_pgpr_3343_1537 186

lcp_pgpr_3343_1352 465

lcp_pgpr_3121_1341 15376

lcp_pgpr_3232_1537 0

lcp_pgpr_3513_1341 0

lcp_pgpr_3513_1352 0

lcp_pgpr_3513_1537 0

k__pgpr_3513_1234 0

I Need to delete table having rowcount value as 0

    proc sql noprint;

      select relname into :del_tables separated by ' '

         from &table..pgpr_rowcnt

          where rowcount =0 ;

    quit;

   %put _TRACE tables needs to be deleted &del_tables;

  /* deleting tables having zero rowcount */

   proc datasets library = &table nolist nowarn memtype=(data view);

   delete &del_tables. ;

   run;

  This is working but since we know we have some limitation of length (max 65,534)  to store value in macro . can anyone please help me is there any other way I can do same process.

Thanks!!!

[Tom]

If the list is longer than can be stored in macro variable then generate code instead of a macro variable.

filename code temp;
data _null_;
  set &table..pgpr_rowcnt end=eof;
  where rowcount =0 ;
  if _n_=1 then put 
  "proc datasets library = &table nolist nowarn memtype=(data view);"
/ "  delete " @
  ;
  put relname  @;
  if eof then put ';' / 'run;quit;' ;
run;
%include code / source2;

[Reeza · Accepted solution]

data _null_;

  set &table..pgpr_rowcnt end=eof;
  where rowcount =0 ;
  if _n_=1 then call execute(
  "proc datasets library = &table nolist nowarn memtype=(data view); delete ");
  call execute(relname);
  call execute (" ");

  if eof then call execute(";run;quit;");

run;

CALL EXECUTE version (untested)

[PaigeMiller]

Or, similar to @Tom 's example but with CALL EXECUTE, there is no limit on how many table names could be listed.

[ballardw]

Tables (SAS data sets) from a library or variables from a data set?

Lets see if you can clean up your description to be a bit clearer of what you have and what you want to do.

You say" table pgpr_rowcnt which will store 100000s of coloumns" and then show an example of exactly 2 columns. So how do those 2 columns relate to Pgpr_rowcnt?

Call execute comes to mind, no macro variable involved.

data _null_;
   set &table..pgpr_rowcnt end=Lastone;
   if _n_=1 then call execute("  proc datasets library = &table nolist nowarn memtype=(data view);");
   if rowcount= 0 then call execute('delete '||relname||';');
   if lastone then call execute ('run; quit;');
run;

This in effect stacks the code for proc datasets into the execution queue.

Or use similar syntax to write the text to a program file and use %include to call the created code. This has the advantage of you can review the created code for syntax problems and have a document of what was done to your data and when.

Note: Proc Datasets uses QUIT to end the procedure because it supports run group processing and a single run might be followed by others, especially if used interactively.