topic Re: How to efficiently delete tables from a library in SAS Programming

How to efficiently delete tables from a library

Aexor — Thu, 28 Sep 2023 15:16:58 GMT

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!!!

Re: How to efficiently delete tables from a library

Tom — Thu, 28 Sep 2023 15:29:52 GMT

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;

Re: How to efficiently delete tables from a library

PaigeMiller — Thu, 28 Sep 2023 15:32:13 GMT

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

Re: How to efficiently delete tables from a library

ballardw — Thu, 28 Sep 2023 15:41:08 GMT

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.

Re: How to efficiently delete tables from a library

Reeza — Thu, 28 Sep 2023 16:05:59 GMT


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)