%if %sysfunc(exist(dest2.&fname.)) work well but not in a call execute

[alepage]

Hello, 

I am using the macro function copydataset(path,fname) (see code below)

%macro copydataset(path,fname);

%let prefix=%substr(&path,1,22);
%let sufix=%substr(&path,24,%length(&path)-%length(&prefix)-1);
%let newpath=&prefix./Data_Retention/&sufix.;
%put &=prefix &=sufix &=newpath;

libname source spde "&path.";
libname dest2 spde "&newpath.";

%if %sysfunc(exist(dest2.&fname.)) %then 
%do;
	%put "the dataset dest2.&fname. already exist at: %sysfunc(pathname(dest2))";
%end;
%else
%do;
				proc copy in=source out=dest2 memtype=data;
    			select &fname;
				run;
%end;
libname source clear;
libname dest2 clear;

%mend copydataset;
%copydataset(/dwh_actuariat/sasdata/sas2001/be/auto,be_auto_prmaou2001);

At the first call, I expect to copy the dataset from source to destination and It work fine.

%copydataset(/dwh_actuariat/sasdata/sas2001/be/auto,be_auto_prmaou2001);
PREFIX=/dwh_actuariat/sasdata SUFIX=sas2001/be/auto NEWPATH=/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto
NOTE: Libref SOURCE was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/sas2001/be/auto/
2                                                          The SAS System                            11:07 Tuesday, November 5, 2024

NOTE: Libref DEST2 was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto/

NOTE: Copying SOURCE.BE_AUTO_PRMAOU2001 to DEST2.BE_AUTO_PRMAOU2001 (memtype=DATA).
NOTE: Composite index POLICYSEQ has been defined.
NOTE: There were 622642 observations read from the data set SOURCE.BE_AUTO_PRMAOU2001.
NOTE: The data set DEST2.BE_AUTO_PRMAOU2001 has 622642 observations and 298 variables.
NOTE: Compressing data set DEST2.BE_AUTO_PRMAOU2001 decreased size by 64.32 percent.
NOTE: PROCEDURE COPY used (Total process time):
      real time           6.91 seconds
      cpu time            7.89 seconds
      

NOTE: Libref SOURCE has been deassigned.
NOTE: Libref DEST2 has been deassigned.

During the second call, I expect to see a put statements into the log, which is the case

%copydataset(/dwh_actuariat/sasdata/sas2001/be/auto,be_auto_prmaou2001);
PREFIX=/dwh_actuariat/sasdata SUFIX=sas2001/be/auto NEWPATH=/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto
NOTE: Libref SOURCE was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/sas2001/be/auto/
NOTE: Libref DEST2 was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto/
"the dataset dest2.be_auto_prmaou2001 already exist at: /dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto/"
NOTE: Libref SOURCE has been deassigned.
NOTE: Libref DEST2 has been deassigned.

But when I do a call execute, the %sysfunc(exist(dest2.&fname)) does not seems to work properly

libname dest1 base "/.../LEGO_3730_Data_Retention/data";

data classicpremfiles;
set dest1.classicpremfiles (firstobs=1 obs=2);
run;

/******************** macro copy dataset from source to destination ********************************/

%macro copydataset(path,fname);

%let prefix=%substr(&path,1,22);
%let sufix=%substr(&path,24,%length(&path)-%length(&prefix)-1);
%let newpath=&prefix./Data_Retention/&sufix.;
%put &=prefix &=sufix &=newpath;

libname source spde "&path.";
libname dest2 spde "&newpath.";

%if %sysfunc(exist(dest2.&fname.)) %then 
%do;
	%put "the dataset dest2.&fname. already exist at: %sysfunc(pathname(dest2))";
%end;
%else
%do;
				proc copy in=source out=dest2 memtype=data;
    			select &fname;
				run;
%end;
libname source clear;
libname dest2 clear;

%mend copydataset;
/*%copydataset(/dwh_actuariat/sasdata/sas2001/be/auto,be_auto_prmaou2001);*/

/***************** Call execute ************/

data _null_;
   set classicpremfiles;
   call execute('%copydataset('||strip(path)||' ,'||strip(fname)||');');   
run;

I expect to receive a message telling me that the file already exit into the log file for the first one, and to copy the second one from source to destination. But both are copy. Why?

libname dest1 base "/finsys/bicoe/BICOE/CR_SR_Prj/LEGO_3730_Data_Retention/data";
NOTE: Libref DEST1 was successfully assigned as follows: 
      Engine:        BASE 
      Physical Name: /finsys/bicoe/BICOE/CR_SR_Prj/LEGO_3730_Data_Retention/data
30         
31         data classicpremfiles;
32         set dest1.classicpremfiles (firstobs=1 obs=2);
33         run;

NOTE: There were 2 observations read from the data set DEST1.CLASSICPREMFILES.
NOTE: The data set WORK.CLASSICPREMFILES has 2 observations and 5 variables.
NOTE: Compressing data set WORK.CLASSICPREMFILES increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

34         
35         /******************** macro copy dataset from source to destination ********************************/
36         
37         %macro copydataset(path,fname);
38         
39         %let prefix=%substr(&path,1,22);
40         %let sufix=%substr(&path,24,%length(&path)-%length(&prefix)-1);
41         %let newpath=&prefix./Data_Retention/&sufix.;
42         %put &=prefix &=sufix &=newpath;
43         
44         libname source spde "&path.";
2                                                          The SAS System                            11:07 Tuesday, November 5, 2024

45         libname dest2 spde "&newpath.";
46         
47         %if %sysfunc(exist(dest2.&fname.)) %then
48         %do;
49         	%put "the dataset dest2.&fname. already exist at: %sysfunc(pathname(dest2))";
50         %end;
51         %else
52         %do;
53         				proc copy in=source out=dest2 memtype=data;
54             			select &fname;
55         				run;
56         %end;
57         libname source clear;
58         libname dest2 clear;
59         
60         %mend copydataset;
61         /*%copydataset(/dwh_actuariat/sasdata/sas2001/be/auto,be_auto_prmaou2001);*/
62         
63         /***************** Call execute ************/
64         
65         data _null_;
66            set classicpremfiles;
67            call execute('%copydataset('||strip(path)||' ,'||strip(fname)||');');
68         run;

PREFIX=/dwh_actuariat/sasdata SUFIX=sas2001/be/auto NEWPATH=/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto
PREFIX=/dwh_actuariat/sasdata SUFIX=sas2001/be/auto NEWPATH=/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto
NOTE: There were 2 observations read from the data set WORK.CLASSICPREMFILES.
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 spde "/dwh_actuariat/sasdata/sas2001/be/auto";
NOTE: Libref SOURCE was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/sas2001/be/auto/
1         +                                                               libname dest2 spde 
"/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto";     proc copy in=source out=dest2 memtype=data;        select 
be_auto_prmaou2001;     run; libname source clear;
NOTE: Libref DEST2 was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto/
NOTE: Copying SOURCE.BE_AUTO_PRMAOU2001 to DEST2.BE_AUTO_PRMAOU2001 (memtype=DATA).
NOTE: Composite index POLICYSEQ has been defined.
NOTE: There were 622642 observations read from the data set SOURCE.BE_AUTO_PRMAOU2001.
NOTE: The data set DEST2.BE_AUTO_PRMAOU2001 has 622642 observations and 298 variables.
NOTE: Compressing data set DEST2.BE_AUTO_PRMAOU2001 decreased size by 64.32 percent.
NOTE: PROCEDURE COPY used (Total process time):
      real time           2.74 seconds
      cpu time            4.21 seconds
      

NOTE: Libref SOURCE has been deassigned.
2         + libname dest2 clear;
NOTE: Libref DEST2 has been deassigned.
2         +                     ;
3                                                          The SAS System                            11:07 Tuesday, November 5, 2024

3         + libname source spde "/dwh_actuariat/sasdata/sas2001/be/auto";
NOTE: Libref SOURCE was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/sas2001/be/auto/
3         +                                                               libname dest2 spde 
"/dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto";     proc copy in=source out=dest2 memtype=data;        select 
be_auto_prmavr2001;     run; libname source clear;
NOTE: Libref DEST2 was successfully assigned as follows: 
      Engine:        SPDE 
      Physical Name: /dwh_actuariat/sasdata/Data_Retention/sas2001/be/auto/
NOTE: Copying SOURCE.BE_AUTO_PRMAVR2001 to DEST2.BE_AUTO_PRMAVR2001 (memtype=DATA).
NOTE: Composite index POLICYSEQ has been defined.
NOTE: There were 590658 observations read from the data set SOURCE.BE_AUTO_PRMAVR2001.
NOTE: The data set DEST2.BE_AUTO_PRMAVR2001 has 590658 observations and 298 variables.
NOTE: Compressing data set DEST2.BE_AUTO_PRMAVR2001 decreased size by 64.38 percent.
NOTE: PROCEDURE COPY used (Total process time):
      real time           2.41 seconds
      cpu time            3.76 seconds
      

NOTE: Libref SOURCE has been deassigned.
4         + libname dest2 clear;
NOTE: Libref DEST2 has been deassigned.
4         +                     ;

How to solve that issue

[Tom · Accepted solution]

Timing.

When you push macro code through CALL EXECUTE it runs IMMEDIATELY and the generated SAS code is stacked up to run later.  In simple code it just makes for a messy SAS log because instead of seeing the macro call in the log you just see the code the macro generated.  But if the macro makes decisions about what code to generate based on the results of running the SAS code it generates then the timing is off.  The macro logic will make decisions before the generated SAS code has had a chance to actually run.

To prevent this just wrap the macro code in %NRSTR() macro quoting function.  Now the macro call will appear in the SAS log and the macro will not run until it is pull back off the stack of commands that CALL EXECUTE generated while the data step was running.

data _null_;
   set classicpremfiles;
   call execute(cats('%nrstr(%copydataset)(path=',path,',fname=',fname,');'));   
run;