issue with the varexist function

[alepage]

Hello, 

I have issue with my if %varexist funtion or may be with the if else condition, because it never goes into the else portion of the script.

libname dest3 spde "/.../Temp/Alain/DR";

data workingpremiumds2 ;
set dest1.workingpremiumds2 (firstobs=1 obs=5);
run;

/********************** Macro function variable exist ********************************************/
%macro varexist
/*----------------------------------------------------------------------
Check for the existence of a specified variable.
----------------------------------------------------------------------*/
(ds        /* Data set name */
,var       /* Variable name */);

/*----------------------------------------------------------------------
Usage Notes:

%if %varexist(&data,NAME)
  %then %put input data set contains variable NAME;

The macro calls resolves to 0 when either the data set does not exist
or the variable is not in the specified data set.
----------------------------------------------------------------------*/

%local dsid rc ;

/*----------------------------------------------------------------------
Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions.
-----------------------------------------------------------------------*/

%let dsid = %sysfunc(open(&ds));

%if (&dsid) %then %do;
  %if %sysfunc(varnum(&dsid,&var)) %then 1;
  %else 0 ;
  %let rc = %sysfunc(close(&dsid));
%end;
%else 0;
%mend varexist;



data _null_;
   set workingpremiumds2;
   call execute
   (compbl(cat(
   "libname source  ", engine,' "',strip(path),'";',   
   '%nrstr(%if %varexist(dest3.',strip(fname),'ORGNL_SAS_DS_ROW_NBR) = 0 %then %do;
    data dest3.',strip(fname),'; ORGNL_SAS_DS_ROW_NBR=_n_; set source.',strip(fname),';run;%end;%else %do;%put ','"the variable already exist";%end;)',    
   "libname source clear;" 
    )));
run;

When I empty the folder /.../Temp/Alain/DR then I run the script, it will create 5 datasets with the variable ORGNL_SAS_DS_ROW_NBR.  But if I select only from data _null_; ...run; the 5 datasets already exist as well as the variable ORGNL_SAS_DS_ROW_NBR so, it should put into the log file that the variable already exits. and not recreate the 5 datasets.  

But it always recreate the 5 datasets so I suspect that it never goes into the else condition.

Please provide some help.

[Quentin]

The macro looks fine to me, but without having the data it's hard to know what's  happening in your CALL EXECUTE step.  I didn't test that it works with the SPDE engine.

I would a %PUT statement to your macro, to have it write to the log the value for the parameters &ds, &var, and also the returned value.  It's possible you'll be surprised by those values.  Something like:

%macro varexist
/*----------------------------------------------------------------------
Check for the existence of a specified variable.
----------------------------------------------------------------------*/
(ds        /* Data set name */
,var       /* Variable name */
,debug=0
);

/*----------------------------------------------------------------------
Usage Notes:

%if %varexist(&data,NAME)
  %then %put input data set contains variable NAME;

The macro calls resolves to 0 when either the data set does not exist
or the variable is not in the specified data set.
----------------------------------------------------------------------*/

%local dsid rc return;

/*----------------------------------------------------------------------
Use SYSFUNC to execute OPEN, VARNUM, and CLOSE functions.
-----------------------------------------------------------------------*/

%let dsid = %sysfunc(open(&ds));

%if (&dsid) %then %do;
  %if %sysfunc(varnum(&dsid,&var)) %then %let return=1;
  %else %let return=0 ;
  %let rc = %sysfunc(close(&dsid));
%end;
%else %let return=0;
%if &debug=1 %then %do ;
  %put macro call %nrstr(%%)VAREXIST(&ds,&var) returns: &return ;
%end ;
&return
%mend varexist;


%put >>%varexist(sashelp.class,name,debug=1)<< ;
%put >>%varexist(sashelp.class,nope,debug=1)<< ;

[Tom · Accepted solution]

Can you explain what you are trying to do?  Your current logic only copies the datasets that do NOT have the variable.  What about the ones that do have the variable?  Wouldn't you want to copy them also?

I suspect you might have generated slightly invalid code? Perhaps a missing comma or an extra comma?  You could try stuffing the result of your string expression into a character variable in the data _null_ step and then PUT it to the SAS log and make sure it generated valid code to pass to CALL EXECUTE().

But trying to use CALL EXECUTE can really mess up the timing of when macro code runs.

Try converting the code generation data step so that it writes the code to a file instead of trying to get CALL EXECUTE to push it onto the stack to run.

So something like this (note this also means you can use the power of the data step PUT statement to make the code simpler and the generated code clearer.

filename code temp;
data _null_;
   set workingpremiumds2;
   file code;
   put "libname source " engine path :$quote. ';' 
     / '%if 0=%varexist(dest3.' fname ',ORGNL_SAS_DS_ROW_NBR) %then %do;'
     / '  data dest3.' fname ';'
     / '   ORGNL_SAS_DS_ROW_NBR=_n_;'
     / '   set source.' fname ';'
     / '  run;'
     / '%end;%else %do;'
     / '  proc copy inlib=source outlib=dest3; select ' fname '; run;'
     / '%end;'
     / 'libname source clear;'
   ;
run;
%include code / source2 ;

Another way would be to wrap your macro logic into a another macro. 

%macro doit(engine,path,fname,varname);
libname source &engine &path ;
%if 0=%varexist(dest3.&fname,&varname) %then %do;
  data dest3.&fname;
     &varname=_n_;
     set source.&fname;
  run;
%end;
%else %do;
   proc copy inlib=source outlib=dest3; select &fname; run;
%end;
libname source clear;
%mend doit;

Then the CALL EXECUTE() code would not have to content with trying to prevent the %IF and %ELSE from running at the wrong time.  But it is still useful to prevent the running of the macro at the wrong time, so wrap the %macroname inside of %NRSTR().

data _null_;
  set workingpremiumds2;
  call execute(cats('%nrstr(%doit)(',engine
     ,',',quote(trim(path))
     ,',',fname
     ,',ORGNL_SAS_DS_ROW_NBR)'))
  ;
run;

PS Why not just use CATS() or CATX() instead of CAT()?  Then you can remove the multiple calls to the STRIP() function and the call to COMPBL().

[alepage]

I want to read a dataset from source, then check if the variable ORGL_SAS_DS_ROW_NBR exist and if not then add the variable and copy the dataset at dest3. Otherwise, put into the log file, the variable already exist.

[Tom]

I still think it would be easier to use the data _null_ step define the libref and to test if the variable exists rather then having to use your %VAREXIST() macro.  Easier to code and much easier to debug.  Plus you can save the status of the test into a dataset rather only to the SAS log.  And you could use CALL EXECUTE() without having to worry about timing issues.

data results;
   set workingpremiumds2;
   length fileref $8 inname outname $41 varnum 8 ;
   rc=libname(fileref,path,engine);
   if rc then do;
      inname=catx('.',fileref,fname);
      dsid=open(inname);
      if dsid then do;
        varnum=(dsid,'ORGNL_SAS_DS_ROW_NBR');
        dsid=close(dsid);
      end;
   end;
   if 0=varnum then do;
      outname=catx('.','dest3',fname);
      putlog 'NOTE: Copying ' inname 'to ' outname 'to add ORGNL_SAS_DS_ROW_NBR.';
      call execute(catx(' '
         ,'data',outname,';ORGNL_SAS_DS_ROW_NBR=_n_;set',inname,';run;'
         ,'libname',libref,'clear;'
      ));
   end;
   else 0<varnum then putlog 'NOTE: ORGNL_SAS_DS_ROW_NBR already exists in ' inname '.';
   else putlog 'NOTE: Unable to find ' fname= engine= path= ;
run;

[Tom]

Here is an example that is similar to what you are trying that illustrates the timing issue of trying to issue macro code via CALL EXECUTE.

data class; set sashelp.class; run;
data _null_;
  call execute('libname source (work);');
  call execute('%put not quoted=%varexist(source.class,sex);');
  call execute('%nrstr(%put) nrstr quoted=%nrstr(%varexist)(source.class,sex);');
  call execute('libname source clear;');
run;

So we are trying to run code that 

1) Sets up a libname.

2) Checks for the existence of a variable in a dataset in that libname.

3) Clears the libname.

The example has two different methods of coding the second item.  One with no macro quoting.  And one with %NRSTR() wrapped around each macro function or macro statement or macro call.

Here is the resulting SAS log

1    data class; set sashelp.class; run;

NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


2    data _null_;
3      call execute('libname source (work);');
4      call execute('%put not quoted=%varexist(source.class,sex);');
5      call execute('%nrstr(%put) nrstr quoted=%nrstr(%varexist)(source.class,sex);');
6      call execute('libname source clear;');
7    run;

not quoted=0
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: CALL EXECUTE generated line.
1   + libname source (work);
NOTE: Libref SOURCE was successfully assigned as follows:
      Levels:           1
      Engine(1):        V9
      Physical Name(1): (system-specific file/path name)
2   + %put nrstr quoted=%varexist(source.class,sex);
nrstr quoted=1
3   + libname source clear;
NOTE: Libref SOURCE has been deassigned.

As you can see the %PUT without the macro quoting ran while the DATA _NULL_ step was still working and since the libref SOURCE had not yet been defined the %VAREXIST() function call did not find the variable.

But the second one ran after the data step.  And after the LIBNAME statement so it did find that SEX existed in SOURCE.CLASS.  Plus you can see the actual source code that CALL EXECUTE() was able to push onto the stack to run after the data _NULL_ step.

[Kurt_Bremser]

I do not think that you need your %VAREXIST macro at all; using the VARNUM function in the DATA step itself will be much easier and straightforward.

I also recommend to write to a temporary file and %INCLUDE that; CALL EXECUTE becomes unwieldy with complex code, and a temporary file allows checking the file before you use it in %INCLUDE.

Example:

data workingpremiumds2;
input path :$200. fname :$32.;
datalines;
/home/kurt.bremser/mylib class
;

data mylib.class;
set sashelp.class;
run;

libname dest3 "~/dest3";

/* up to here, everything is just preparation */

%let checkvar = ORGNL_SAS_DS_ROW_NBR;

filename incfile temp;

data _null_;
set workingpremiumds2;
file incfile;
length fileref $8 line $80;
fileref = "SOURCE";
rc = libname(fileref,path);
if rc = 0 or findw(sysmsg(),"ERROR") = 0
then do;
  did = open(catx(".",fileref,fname));
  putlog did=;
  if did
  then do;
    rc = varnum(did,upcase("&checkvar."));
    if rc = 0
    then do;
      line = cats("libname source '",path,"';");
      put line;
      line = cats("data dest3.",fname,";");
      put line;
      line = cats("set source.",fname,";");
      put line;
      line = "&checkvar. = _n_;";
      put line;
      put "run;";
      put "libname source clear;";
    end;
    rc = close(did);
  end;
  rc = libname(fileref,"");
end;
run;

data _null_;
infile incfile;
input;
put _infile_;
run;