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;
... View more
