Using call execute with a macro function is not working properly

[alepage]

Hello,

I have created the follow varexist2 macro function, which send into temp if the various variable defined in varlist are present or not into the provide dataset name. Then the information is append into temp2 each time the macro function varexist2 is called.

Alone, the call look like that.

%varexist2(dsn2=temp.be_auto_prmaou2001);

and I can make thousand of macro execution and it works fine.

However, I should be using a call execute like below:

temp is only populated with dsn2 name. I am loosing the answers about if a variables exist or not into the datasets.

First question how do we troubleshoot the call execute?

How do we solve that issue?

proc printto log='~/eg.log';
libname dest1 base '/.../data';
data prm_FILESLST_SPDS9 (drop=text cfname);
set dest1.FILESLST_SPDS9;
if index(fname,'prm') = 0 then delete;
if index(fname,'raw') > 0 then delete;
if index(fname,'_ab_') > 0 then delete;
if index(text,'/test/') > 0 then delete;
if index(path,'/vd/') > 0 then delete;
if cie = 'sa' then delete;
process='HYFI';
where category eq 'prime';
run;
data prm_FILESLST_SPDS9 ;
set prm_FILESLST_SPDS9 ;
path2=scan(path,6,'/');
if path2 ne '' then delete;
run;

proc sort data=prm_FILESLST_SPDS9 (drop=path2) nodupkey out=HYFI_prime;
by path fname;
run;
proc sql;
create table valid_HYFI_prime as
select *, count(fname) as count
from HYFI_prime
group by fname
having count(fname) > 1;
quit;
data valid_HYFI_prime;
set valid_HYFI_prime;
run;
data dest1.HYFI_prime;
retain process path fname subfolder hub extension path_n_fname cie lob category type; 
set HYFI_prime;
run;
data HYFI_prime;
set dest1.HYFI_prime;
run;

data test;
set HYFI_prime;
if index(fname,'prm') = 0 then output test;
run;

%let Cl_auto_prm_varlst=AGREEMENT_NBR APPLICATION_ID BUSINESS_SOURCE_CD COMPANY_CD DISTRIBUTION_COMPANY_CD GC_COVER_CATEGORY_CD OBJECT_TYPE 
                        POLICY_EXPIRY_DT POLICY_INCEPTION_DT POLICY_TYPE_TR PROVINCE_CD TRANSACTION_CD_SUMMARY;
%let Cl_prop_prm_varlst=AGREEMENT_NBR APPLICATION_ID BROKER_NBR BUSINESS_SOURCE_CD COMPANY_CD DISTRIBUTION_COMPANY_CD OBJECT_TYPE 
                        POLICY_EXPIRY_DT POLICY_INCEPTION_DT POLICY_TYPE_TR PROVINCE_CD REINSURER_CD TRANSACTION_CD_SUMMARY;

%let Ha_auto_prm_varlst=AGREEMENT_NBR BROKER_FMT_GC BUSINESS_SOURCE_CD COMPANY_CD POLICY_EXPIRY_DT POLICY_INCEPTION_DT POLICY_TYPE_CD 
                        RISK_PROVINCE_CD RISK_TYPE_CD TRANSACTION_CD_SUMMARY;
%let Ha_prop_prm_varlst=AGREEMENT_NBR BROKER_FMT_GC BUSINESS_SOURCE_CD COMPANY_CD POLICY_EXPIRY_DT POLICY_INCEPTION_DT POLICY_TYPE_CD PREMIUM_TRANSACTION_CD 
                        REINSURANCE_TREATY POLICY_PROVINCE_CD RISK_CLASS SECTOR_CD TRANSACTION_CD_SUMMARY;

 data  temp2;
 set _null_;
 run;

%macro varexist2(dsn2=) ;
%global Cl_auto_prm_varlst Cl_prop_prm_varlst Ha_auto_prm_varlst Ha_prop_prm_varlst varlist;
 data temp;
 run;

%IF %SUBSTR(&DSN2.,9,4) eq auto %then 
	%do;
		%let subfolder=%str(auto);
		%let year=%SUBSTR(&DSN2.,20,4);
		%let month=%SUBSTR(&DSN2.,17,3);
		%let cie=%SUBSTR(&DSN2.,6,2);		
		%if (&cie. eq be) or (&cie. eq gc) or (&cie. eq gp) %then %let varlist=&Cl_auto_prm_varlst.;
		%else %let varlist=&Ha_auto_prm_varlst.;
	%end;
%ELSE %IF %SUBSTR(&DSN2.,9,4) eq prop %then 
	%do;
		%let subfolder=%str(habi);
        %let year=%SUBSTR(&DSN2.,20,4);
		%let month=%SUBSTR(&DSN2.,17,3);
		%let cie=%SUBSTR(&DSN2.,6,2);
		%if (&cie. eq be) or (&cie. eq gc) or (&cie. eq gp) %then %let varlist=&Cl_prop_prm_varlst.;
		%else %let varlist=&Ha_prop_prm_varlst.;
	%end;
%ELSE %IF %SUBSTR(&DSN2.,9,3) = cna %then 
	%do;
	    
		%let subfolder=%str(entr);
		%let year=%SUBSTR(&DSN2.,19,4);
		%let month=%SUBSTR(&DSN2.,16,3);
		%let cie=%SUBSTR(&DSN2.,6,2);
		%if (&cie. eq be) or (&cie. eq gc) or (&cie. eq gp) %then %let varlist=&Cl_prop_prm_varlst.;
	%end;

/*%put &=cie &=subfolder &=month. &=year &=varlist.;*/
libname temp spde "/dwh_actuariat/sasdata/sas&year./&cie./&subfolder./";
/*%put %sysfunc(pathname(TEMP));*/

%local dsid vnum;


%do i=1 %to %sysfunc(countw(&varlist.));
	%let var&i=%scan(&varlist.,&i);
%end;

data temp;
set temp;

%let dsid = %sysfunc(open(&dsn2));
%if &dsid %then 
%do;
		    
			%do j=1 %to %sysfunc(countw(&varlist.));
				%let vnum&j=%sysfunc(varnum(&dsid,&&var&j));
				%if &&vnum&j > 0 %then 
				%do;
					%scan(&varlist.,&j) = 'Y';;
				%end;
				%else 
				%do;
					%scan(&varlist.,&j) ='N';;
				%end;
			%end;
%let dsid = %sysfunc(close(&dsid));		
%end; 
run;
data temp;
length fname2 $35.;
set temp;
fname2="&dsn2.";
run;

data temp2;
set temp temp2;
run;

%put &dsn2.;
%mend varexist2;

/*%varexist2(dsn2=temp.be_auto_prmaou2001);*/

/*data HYFI_prime;*/
data test2 (keep=txt fname);
length txt $100.;
set HYFI_prime (obs=36);
txt=COMPRESS(cats('%varexist2(dsn2=temp.',fname,');'));
run;

data _null_;
set test2;
call execute('%varexist2(dsn2=temp.'||strip(fname)||');');        
run;

[SASJedi]

Try using DOSUBL instead. 

[ballardw]

Debug macros using the system options MPRINT MLOGIC (if branches in code aren't working as expected) SYMBOLGEN (if getting unexpected names and such generated).

Then look at the LOG for where odd things happen.

Then share the pertinent part of the Log.

I will say that looking for part of a parameter to be in specific locations of a parameter string is very likely to be fragile, thos %substr(&DSN2   ) calls.

You make a data set TEST2 with a variable named TXT that appears to hold the text that you want to use for call execute. Then when you use Test2 you rebuild a string for call execute.

Why not:  Call execute(txt);

Did you not make TXT long enough to hold all the parameters? Have you examined the values of TXT in your Test2 set?

Another common option is instead of CALL Execute is to create your string of the command, such as TXT. Then write it to a text file using a data step. Then %include that file to execute it.

[Mazi · Accepted solution]

You certainly have timing issues.

Remember, when calling a macro with call execute, all the macro code is executed immediately.

The macro you’ve provided also contains non macro code, which gets added to the call stack and executed when the datastep terminates.

You can also try delaying the macro call with a quoting function such as %nrstr

eg: call execute (‘%nrstr(<your macro call>’)’);

[Reeza]

Are you trying to add variables to a dataset to meet a standard in case it doesn't have all the required variables, with 4 different iterations?

[alepage]

Yes temp should look like that:

fname2                                           agreement_nbr   ...     transaction_cd_summary

temp.be_auto_prmaug2021              Y                     ...                Y

Then this temp file is append into temp2 for each macro function call      

[Reeza]

You can use a macro, but it would be more efficient IMO to have this be table driven than macro/string driven. 

• Create 4/5 empty tables with the desired structures, templates. 
• For each table of interest, determine right comparison (same as now to some degree)
• Use PROC COMPARE or use sashelp.vcolumn and a merge to compare the variables presence