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;
Try using DOSUBL instead.
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.
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
You can use a macro, but it would be more efficient IMO to have this be table driven than macro/string driven.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.