How To find the value of a column present in multiple tables in SAS. The column name is same.

[Rhino84]

In Multiple Tables in the same schema, there is a same column source_code which has multiple values.

I want to find the tables name where same column source_code = 'KL'.

table 1				table 2
x	y	source_code		z	f	source_code
		LL				KL
table 3				table 4
s	g	source_code		v	n	source_code
		KL				MO

[Tom]

So let's write some code that will do it for a couple of datasets (assuming that is what you meant by "table").

data want;
   length dsn dataset $41 ;
   set table1(keep=source_code) table2(keep=source_code) indsname=dsn;
   where source_code = 'KL';
   if dsn ne lag(dsn);
   dataset=dsn;
run;

Now to automate this for a lot of datasets you could use PROC CONTENTS (or perhaps DICTIONARY.COLUMNS) to find the names of all of the datasets that have that variable.

proc contents noprint data=mylib._all_ out=contents; run;

Then use the list to drive the code generation.

data _null_;
  set contents end=eof;
  where upcase(name)='SOURCE_CODE';
  if _n_=1 then call execute('data want;length dsn dataset $41 ;  set');
  call execute(cats(libname,'.',memname,'(keep=source_code)'));
  if eof then call execute(' indsname=dsn;'
      || "where source_code = 'KL';if dsn ne lag(dsn);dataset=dsn;run;");
run;

[Ksharp]

If you have SAS/IML module, could try this one:

data a;
input source_code : $40. b c;
cards;
KL  1234567891 1234567891
wwrew 232442 343
;
run;
data b;
input source_code : $40. bb xxxx : $20.;
cards;
KL 233 2323
kl  1234567891 1234567891
;
run;
data c;
input source_code : $40. bbbbbb;
cards;
Thisaccount 1234567891
KLsaccount 1234567891 
;
run;




%let library= work ;  *the library you need to search;
%let vname= source_code; *the variable in common you need to search;
%let string= KL ; *the string you need to search, it could be  uppercase or lowcase;

proc delete data=want;run;
proc iml;
dsn="&library.."+datasets("&library.");
do i=1 to nrow(dsn);
use (dsn[i]);
 read all var {&vname.} into x;
 flag=(upcase(x)="%upcase(&string)"); 
 if any(flag=1) then do;
   loc=loc(flag); 
   obs=obs//t(loc);
   table=table//repeat(dsn[i],ncol(loc)); 
 end;
close (dsn[i]);
end;
create want var {table  obs};
append;
close;
quit;

proc print noobs;run;

[Rhino84]

i GET THIS ERROR MESSAGE.

ERROR: File PCRDATA.AC_ANLYS_AR.DATA is sequential. This task requires reading observations in a random order, but the engine allows only sequential access.

[Ksharp]

I got nothing from LOG.

1
2    data a;
3    input source_code : $40. b c;
4    cards;

NOTE: 数据集 WORK.A 有 2 个观测和 3 个变量。
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.42 秒
      CPU 时间          0.06 秒


7    ;
8    run;
9    data b;
10   input source_code : $40. bb xxxx : $20.;
11   cards;

NOTE: 数据集 WORK.B 有 2 个观测和 3 个变量。
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.00 秒
      CPU 时间          0.00 秒


14   ;
15   run;
16   data c;
17   input source_code : $40. bbbbbb;
18   cards;

NOTE: 数据集 WORK.C 有 2 个观测和 2 个变量。
NOTE: “DATA 语句”所用时间（总处理时间）:
      实际时间          0.02 秒
      CPU 时间          0.01 秒


21   ;
22   run;
23
24
25
26
27   %let library= work ;  *the library you need to search;
28   %let vname= source_code; *the variable in common you need to search;
29   %let string= KL ; *the string you need to search, it could be  uppercase or lowcase;
30
31   proc delete data=want;run;

WARNING: 文件“WORK.WANT.DATA”不存在。
NOTE: “PROCEDURE DELETE”所用时间（总处理时间）:
      实际时间          0.00 秒
      CPU 时间          0.00 秒


32   proc iml;
NOTE: 正在写入 HTML Body（主体）文件: sashtml.htm
NOTE: IML Ready
33   dsn="&library.."+datasets("&library.");
34   do i=1 to nrow(dsn);
35   use (dsn[i]);
36    read all var {&vname.} into x;
37    flag=(upcase(x)="%upcase(&string)");
38    if any(flag=1) then do;
39      loc=loc(flag);
40      obs=obs//t(loc);
41      table=table//repeat(dsn[i],ncol(loc));
42    end;
43   close (dsn[i]);
44   end;
45   create want var {table  obs};
46   append;
47   close;
NOTE: Closing WORK.WANT
NOTE: 数据集 WORK.WANT 有 3 个观测和 2 个变量。
48   quit;
NOTE: Exiting IML.
NOTE: “PROCEDURE IML”所用时间（总处理时间）:
      实际时间          1.71 秒
      CPU 时间          0.31 秒


49
50   proc print noobs;run;

NOTE: 从数据集 WORK.WANT. 读取了 3 个观测
NOTE: “PROCEDURE PRINT”所用时间（总处理时间）:
      实际时间          0.08 秒
      CPU 时间          0.01 秒

But if you are using CAS engine or other engines ,not BASE engine. Could try this one (v9 engine). if it was still not work, then try Tom 's code .

libname x v9 'c:\temp\';  

data x.a;
input source_code : $40. b c;
cards;
KL  1234567891 1234567891
wwrew 232442 343
;
run;
data x.b;
input source_code : $40. bb xxxx : $20.;
cards;
KL 233 2323
kl  1234567891 1234567891
;
run;
data x.c;
input source_code : $40. bbbbbb;
cards;
Thisaccount 1234567891
KLsaccount 1234567891 
;
run;




%let library= x ;  *the library you need to search;
%let vname= source_code; *the variable in common you need to search;
%let string= KL ; *the string you need to search, it could be  uppercase or lowcase;






proc delete data=want;run;
proc iml;
dsn="&library.."+datasets("&library.");
do i=1 to nrow(dsn);
use (dsn[i]);
 read all var {&vname.} into x;
 flag=(upcase(x)="%upcase(&string)"); 
 if any(flag=1) then do;
   loc=loc(flag); 
   obs=obs//t(loc);
   table=table//repeat(dsn[i],ncol(loc)); 
 end;
close (dsn[i]);
end;
create want var {table  obs};
append;
close;
quit;

proc print noobs;run;

[yabwon]

You could use the BasePlus package's macro %findDSwithVarVal() to get list of data sets with number of the firs obs. containing the value you need.

%loadPackage(BasePlus) 

libname ABC "/path/"; 

data ABC.a;
input source_code : $40. b c;
cards;
KL  1234567891 1234567891
wwrew 232442 343
;
run;
data ABC.b;
input source_code : $40. bb xxxx : $20.;
cards;
XX 233 2323
aa 233 2323
KL 233 2323
kl  1234567891 1234567891
;
run;
data ABC.c;
input source_code : $40. bbbbbb;
cards;
Thisaccount 1234567891
KLsaccount 1234567891 
;
run;

%findDSwithVarVal(source_code, KL, lib=abc, result=work.result)

proc print data=work.result;
run;

Printout:

The macro search all data sets in a given lib and checks if the variable contains given value. Number of the first obs. with the value you search for is provided.

There are other parameters to modify behavior of the %findDSwithVarVal() macro.

Bart