Date:         Tue, 24 Jul 2007 02:06:05 -0700
Reply-To:     RolandRB <rolandberry@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         RolandRB <rolandberry@HOTMAIL.COM>
Organization: http://groups.google.com
Subject:      Re: problems with using CALL EXECUTE in a loop
Comments: To: sas-l@uga.edu
In-Reply-To:  <ZsqdnbM4KM9LCzjbRVnzvAA@telenor.com>
Content-Type: text/plain; charset="us-ascii"

On 24 Jul, 08:05, "Rune Runnestø" <r...@fastlane.no> wrote:
> I want to selectively delete certain tables based on the tablenames.
> I have made some example tables here, and a macro that works
> except the last data _null_ step in it.
> I have pasted the error message from the log below. I don't understand
> what SAS is telling me in the log. If a do-end loop in a sequence of outer
> call execute statements is not possible how can I traverse the _TMP_SAVELIST
> and make a macro variable of the concatenation of the table names, with a
> space between them ? If that could be an adequat alternative.
>
> data __tab1;
>    attrib x length = $10;
> run;
> data __tab2;
>    attrib x length = $10;
> run;
>
> data _tab3;
>    attrib x length = $10;
> run;
>
> data tab4;
>    attrib x length = $10;
> run;
> data tab5;
>    attrib x length = $10;
> run;
>
> %macro delete_certain_tables;
>    proc sql;
>       create table __tmp_savelist as
>          select memname
>          from dictionary.tables
>          where libname = "WORK"
>          and substr(memname, 1, 2) eq '__'
>       ;
>    quit;
>    data _null_;
>       set __tmp_savelist;
>       call symput ("_nr", _n_);
>       call symput ("_dsname"   || put(_n_, 8. -L), compress(memname));
>    run;
>
>    data _null_;
>       call execute ("proc datasets library = WORK" );
>       call execute ("   save " );
>       %do i = 1 %to &_nr;
>          call execute ("   &&_dsname&i " );
>       %end;
>       call execute ("   ;" );
>       call execute ("quit;" );
>    run;
> %mend;
> %delete_certain_tables;
>
> 1    + proc datasets library = WORK
> NOTE: Line generated by the CALL EXECUTE routine.
> 2    +    save
>           ----
>           22
>           76
> ERROR 22-322: Syntax error, expecting one of the following: ;, ALTER, DD,
> DDNAME, DETAILS, FORCE,
>               GENNUM, KILL, LIB, LIBRARY, MEMTYPE, MT, MTYPE, NODETAILS,
> NOFS, NOLIST, NOWARN,
>               PROTECT, PW, READ.
> ERROR 76-322: Syntax error, statement will be ignored.
> 3    +    __TAB1
> 4    +    __TAB2
> 5    +    __TMP_SAVELIST
> 6    +    ;
> NOTE: Enter RUN; to continue or QUIT; to end the procedure.
> 7    + quit;
>
> NOTE: The SAS System stopped processing this step because of errors.
>
> Regards, Rune

Can't you use the ":" expansion for datasets like this? It works for
me!

data __w1;
  dummy="dummy";
run;

data __w2;
  dummy="dummy";
run;

proc datasets nolist lib=work;
  delete __:;
run;
quit;



NOTE: Deleting WORK.__W1 (memtype=DATA).
NOTE: Deleting WORK.__W2 (memtype=DATA).
12   quit;

NOTE: PROCEDURE DATASETS used (Total process time):
      real time           0.17 seconds
      cpu time            0.00 seconds