topic Re: Drop work table conditionally in SAS Programming

Drop work table conditionally

DavidPhillips2 — Mon, 04 Feb 2019 20:17:00 GMT

Is there a way to drop a table and conditionally if it exists?

Drop Work Table Conditionally

DavidPhillips2 — Mon, 04 Feb 2019 20:17:44 GMT

Is there a way to drop a table and conditionally if it exists?

Re: Drop Work Table Conditionally

ballardw — Mon, 04 Feb 2019 20:50:35 GMT

This is one way:

data work.class;
   set sashelp.class;
run;

data _null_;
 if exist ("WORK.CLASS") then call execute("proc datasets library=work; delete CLASS;run;quit;") ;
run;

however if your "table" is in a DBMS you likely need to use other tools;

Re: Drop Work Table Conditionally

DavidPhillips2 — Mon, 04 Feb 2019 21:20:41 GMT

I did a proc sql drop for about 50 work tables and it seems to work ok.

Re: Drop work table conditionally

Patrick — Mon, 04 Feb 2019 22:00:32 GMT

@DavidPhillips2 wrote:

Is there a way to drop a table and conditionally if it exists?

If this is just about avoiding an error condition when trying to delete a table which doesn't exist (as it would happen with a SQL DROP) then I normally use PROC DATASETS with the NOWARN option.

data work.class;
  set sashelp.class;
run;

proc datasets lib=work nolist nowarn;
  delete class otherTable;
  run;
quit;

Re: Drop work table conditionally

DavidPhillips2 — Mon, 04 Feb 2019 22:27:00 GMT

Very similar question: If a table does not exist and I'm trying to rename a column. Is there a way to handle this?

data a; set b (rename=(&renamePeriods)) c;

run;

But table b does not have columns because of an earlier step.

Re: Drop work table conditionally

ballardw — Mon, 04 Feb 2019 23:29:02 GMT

@DavidPhillips2 wrote:

Very similar question: If a table does not exist and I'm trying to rename a column. Is there a way to handle this?

data a; set b (rename=(&renamePeriods)) c;

run;

But table b does not have columns because of an earlier step.

Proc datasets lets you modify data in place. So the example I provided with small modifications would allow you to change a variable name, label or format. Instead of Delete you would be looking at Modify with Rename in Datasets syntax.

It would not work as a data set option in the code you show but could be run prior to setting the datasets together.

Re: Drop work table conditionally

ScottBass — Tue, 05 Feb 2019 00:56:11 GMT

Dunno if this will help. But it's not conditional, it just attempts to delete, and doesn't issue an error or warning if the dataset does not exist (which of course is the equivalent outcome of deletion).

https://github.com/scottbass/SAS/blob/master/Macro/kill.sas

Re: Drop work table conditionally

DavidPhillips2 — Tue, 05 Feb 2019 13:11:35 GMT

How do I check if a table does exist and has no columns?

The logic would be similar to something like this:

data old;
input ID SCORE1 SCORE2;
cards;
24 100 97
28 98 87
60 100 97
65 100 98
70 99 97
40 97 99
190 100 99
196 100 100
210 98 85
;
run;

%macro VarExist(ds, var);
%local rc dsid result;
%let dsid = %sysfunc(open(&ds));

%if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
%let result = 1;
%put NOTE: Var &var exists in &ds;
%end;
%else %do;
%let result = 0;
%put NOTE: Var &var not exists in &ds;

data old;

set old;

score3=0;

run;
%end;

%let rc = %sysfunc(close(&dsid));

%mend VarExist;

%VarExist(old, score3);

https://communities.sas.com/t5/SAS-Programming/SAS-query/m-p/350433#M81457

When I run this:

ERROR: You cannot open WORK.OLD.DATA for output access with member-level control because WORK.OLD.DATA is in use by you in resource
environment DATASTEP (2).

because the table already exists...

Re: Drop work table conditionally

Tom — Tue, 05 Feb 2019 14:06:50 GMT

@DavidPhillips2 wrote:

Very similar question: If a table does not exist and I'm trying to rename a column. Is there a way to handle this?

data a; set b (rename=(&renamePeriods)) c;

run;

But table b does not have columns because of an earlier step.

There are SAS options to control messages for issue with DROP KEEP RENAME dataset options.

 DKRICOND=ERROR    Specifies the error level to report when a variable is missing from an input
                   data set during the processing of a DROP=, KEEP=, or RENAME= data set option.
 DKROCOND=WARN     Specifies the error level to report when a variable is missing from an output
                   data set during the processing of a DROP=, KEEP=, or RENAME= data set option.

Re: Drop work table conditionally

ballardw — Tue, 05 Feb 2019 16:37:56 GMT

The error means what it says, somewhere you are using the data set referenced. I don't see any CLOSE before the data step that attempts to use the same data set, so that's the likely culprit in your macro.

One example of finding out if a data set that exists has any variables:

data work.junk;
run;

proc sql;
   select nvar into : nvars
   from dictionary.tables
   where libname='WORK' and 
         memname='JUNK'
   ;
run;

%put Number of Vars in Work.Junk is &nvars.;

Re: Drop work table conditionally

Tom — Tue, 05 Feb 2019 16:46:42 GMT

Your macro is opening the dataset to check the variable names and then running a data step to overwrite that dataset while it is still open.

You should close it BEFORE trying to change the dataset.

If you did want to make a variable if it doesn't already exist then you could do something like this instead.

%macro AddVar(ds, var);
%local rc dsid result;
%let dsid = %sysfunc(open(&ds));
%if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
  %let result = 1;
  %put NOTE: Var &var exists in &ds;
%end;
%else %do;
  %let result = 0;
  %put NOTE: Var &var not exists in &ds;
%end;
%let rc = %sysfunc(close(&dsid));
%if (&result=0) %then %do;

data &ds;
  set &ds;
  &var=0;
run;
%end;
%mend AddVar;

%AddVar(old, score3);

Re: Drop work table conditionally

ScottBass — Wed, 06 Feb 2019 02:02:00 GMT

@DavidPhillips2 wrote:
How do I check if a table does exist and has no columns?

SB: How do you encounter this situation? I mean, it's possible, but in all my years of SAS programming I've very rarely if ever encountered it. And I've never had to trap for it.

The logic would be similar to something like this:

data old;
input ID SCORE1 SCORE2;
cards;
24 100 97
28 98 87
60 100 97
65 100 98
70 99 97
40 97 99
190 100 99
196 100 100
210 98 85
;
run;
%macro VarExist(ds, var);
%local rc dsid result;
%let dsid = %sysfunc(open(&ds));
%if %sysfunc(varnum(&dsid, &var)) > 0 %then %do;
%let result = 1;
%put NOTE: Var &var exists in &ds;
%end;
%else %do;
%let result = 0;
%put NOTE: Var &var not exists in &ds;
data old;
set old;
score3=0;
run;
%end;
%let rc = %sysfunc(close(&dsid));
%mend VarExist;
%VarExist(old, score3);

SB: Your macro is testing for the existence of a particularly named variable. Not "...check if a table does exist and has no columns". So which is it? What is your actual problem?

https://communities.sas.com/t5/SAS-Programming/SAS-query/m-p/350433#M81457

When I run this:

ERROR: You cannot open WORK.OLD.DATA for output access with member-level control because WORK.OLD.DATA is in use by you in resource
environment DATASTEP (2).

because the table already exists...

Check:

https://github.com/scottbass/SAS/blob/master/Macro/varexist.sas

https://github.com/scottbass/SAS/blob/master/Macro/varlist.sas

https://github.com/scottbass/SAS/blob/master/Macro/varlist2.sas

Example calls:

options mprint;

data empty;
   stop;
run;

%put %varexist(sashelp.doesnotexist,foo);
%put %varexist(sashelp.class,sex);
%put %varexist(sashelp.class,foo);
%put %varexist(empty,foo);
%put %varexist(empty);  * error, var is required ;

%put %varlist(sashelp.doesnotexist);  * error, dataset must exist ;
%put %varlist(sashelp.class);
%put %varlist(empty);

%let empty=%eval(%varlist(sashelp.class) eq %str());  * test for empty string (or modify the macro) ;
%put &=empty;
%let empty=%eval(%varlist(empty) eq %str());  * test for empty string ;
%put &=empty;

%let varlist=;
%varlist2(sashelp.class (keep=name age sex));
%put &=varlist;

%let varlist=;
%varlist2(sashelp.class (keep=_numeric_));
%put &=varlist;

%let varlist=;
%varlist2(empty);
%put &=varlist;

Read the macro header for other use cases...