topic Re: Searching a specific value in many dataset then putting some info into another dataset in SAS Enterprise Guide

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

alepage — Wed, 13 Mar 2024 18:23:51 GMT

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;

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

Mazi — Wed, 13 Mar 2024 19:00:17 GMT

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

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

alepage — Wed, 13 Mar 2024 19:15:53 GMT

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?

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

Reeza — Wed, 13 Mar 2024 19:21:24 GMT

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;

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

Mazi — Wed, 13 Mar 2024 19:36:49 GMT

%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.

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

alepage — Wed, 13 Mar 2024 19:34:01 GMT

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

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

Reeza — Wed, 13 Mar 2024 22:01:59 GMT

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;

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

alepage — Wed, 13 Mar 2024 23:34:21 GMT

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;