BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Aexor
Lapis Lazuli | Level 10
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!!!
 
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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)

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

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
Super User

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
Diamond | Level 26

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

--
Paige Miller
ballardw
Super User

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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 902 views
  • 6 likes
  • 5 in conversation