data lookup;
input agreement_nbr $15.;
datalines;
XXXXX47187
XXXXX447187
XXXX5642
;
run;
%let monthlist=aou avr dec fev jan jul jun mai mar nov oct sep;
%let yearlist=2006 2007 2008 2009 2010;
%let subfolder=auto habi entr;
%let loblist=auto prop cna;
%let cielist=nx;
%macro searchpolicies;
Data found_agreements;
length agreement_nbr $20. filename $25. libname $100. ;
run;
%do h=1 %to %sysfunc(countw(&cielist.));
%do i=1 %to %sysfunc(countw(&loblist.))-1;
%do k=1 %to %sysfunc(countw(&yearlist.));
%do j=1 %to %sysfunc(countw(&monthlist.));
%let fname=%scan(&cielist.,&h)_%scan(&loblist.,&i)_prm%scan(&monthlist.,&j)%scan(&yearlist.,&k);
%let libnm=/dwh_actuariat/sasdata/sas%scan(&yearlist.,&k)/%scan(&cielist.,&h)/%scan(&subfolder.,&i)/;
%put &=libnm &=fname.;
libname src1 spde "&libnm.";
%put &libnm.&fname.;
proc sql;
create table table_info as
select distinct agreement_nbr as agreement_nbr length=20,
"&fname" as filename length=25,
"&libnm" as libname length=100
from src1.&fname.
where agreement_nbr in (select agreement_nbr from lookup);
quit;
%if &sqlobs ne 0 %then
%do;
proc append base=found_agreements data=table_info force;
run;
%end;
proc sql;
drop table table_info;
quit;
%end;
%end;
%end;
%end;
proc sort data=found_agreements;
by libname filename agreement_nbr;
run;
%mend searchpolicies;
%searchpolicies;
... View more