Searching a specific value in many dataset then putting some info into another dataset

[alepage]

Imagine that I have many datasets like

dataset1

dataset2 

.

.

.

dataset1000

Then I have an agreement_nbr like PPPPP0447187

Now I have created a macro function to seach this value in many dataset, except that I am sendig the agreement_nbr, the path and filename into the log file.

How can we send this information into Data want, for example.

Imagine that I have many agreement_nbr to search for and some basic information surch as path, agreement_nbr and filename into data want.  How do we do that.?

%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 test;
Data temp;
length filename $25. libname $100. policyfound $20.;
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.";
				data _null_;
                	set src1.&fname;
					%let agreement_nbr=PPPPP0447187;
					if agreement_nbr eq "&agreement_nbr." then    
					do;
						put "the policy &agreement_nbr exist in &libnm/&fname.";
					end;
				run; 
			%end;
		%end;
	%end;
%end;


%mend test;
%test;

[Mazi]

If I understand you correctly,

You can create a dataset called “want” with the expected columns and 0 rows.

Then instead of using the put to route output to the log, output to a temporary dataset, and finally use proc append to keep adding rows to your want data.

eg: 

data want;
	length message $1000;
	stop;
	call missing(message);
run;

%macro test;

		%do i=1 %to 100;
			data have;
				length message $1000;
				message="Dataset &i";
			run;

			proc append base=want data=have nowarn;
			run;
		%end;

%mend;

%test

[alepage]

Imagine that I am looking for this value =PPPP0447187  in the variable Agreement_Nbr into 100 datasets.

When found into a specific data set, I want to put into dataset want the following information:

agreement_nbr

path

dataset name

who do we do that?

[Mazi]

%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 test;
Data temp;
length filename $25. libname $100. policyfound $20.;
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.";
				data check;
					length path dataset agreement_nbr $1000;
                	set src1.&fname;
					%let agreement_nbr=PPPPP0447187;
					if agreement_nbr eq "&agreement_nbr." then    
					do;
						path = "&libnm/&fname.";
						dataset = "&fname";
                                                output;
						stop;
					end;
					keep path dataset agreement_nbr;
				run; 
				proc append base=want data=check nowarn;
				run;
			%end;
		%end;
	%end;
%end;

%mend test;



data want;
	length path dataset agreement_nbr $1000;
	stop;
	call missing(of _all_);
run;
%test;

I've adjusted your solution to show what i am thinking. This is untested though, but I believe it will work.

[Reeza]

Do you need to identify which specific agreement number is in the file or just that an agreement is found?

If just that an agreement in the list is found, then SQL is a better option. If you need to find the specific agreement, then a data step approach with a a temporary array is a better approach. 

https://gist.github.com/statgeek/2f733d27820f43fa37d6ba92c30f22cf

---

@alepage wrote:

Imagine that I have many datasets like

dataset1

dataset2 

.

.

.

dataset1000

Then I have an agreement_nbr like PPPPP0447187

Now I have created a macro function to seach this value in many dataset, except that I am sendig the agreement_nbr, the path and filename into the log file.

How can we send this information into Data want, for example.

Imagine that I have many agreement_nbr to search for and some basic information surch as path, agreement_nbr and filename into data want.  How do we do that.?

%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 test;
Data temp;
length filename $25. libname $100. policyfound $20.;
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.";
				data _null_;
                	set src1.&fname;
					%let agreement_nbr=PPPPP0447187;
					if agreement_nbr eq "&agreement_nbr." then    
					do;
						put "the policy &agreement_nbr exist in &libnm/&fname.";
					end;
				run; 
			%end;
		%end;
	%end;
%end;


%mend test;
%test;

---

[alepage]

For example,

if the agreement_nbr PPPP0447187 is found in dataset data1, data10, data28, data51 then

I would like to see in table want this information

agreement_nbr  filename libname

PPPP0447187  data1          lib1

PPPP0447187  data10           lib10

PPPP0447187  data28           lib50

PPPP0447187  data51           lib100

[Reeza · Accepted solution]

Ok, assume you have a table with the agreement number with the same variable name and it is called LOOKUP. 

Untested as no data but this approach should work, rough code below:

• Use a SQL query with a WHERE statement to select from the lookup table, use Distinct to keep only one record of each agreement incase of duplicates. 
• Use the automatic macro variable SQLOBS to determine if any records were selected
• If records are selected, append to master table.
• Delete temporary table from step 1 and repeat. 

%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 test;
Data temp;
length filename $25. libname $100. policyfound $20.;
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.";
				
                                proc sql;
                                create table table_info as
                                select distinct agreement_nbr, "&fname" as filename, "&libnm" as libname from &libnm..&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;


%mend test;
%test;

[alepage]

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;