Find a value from a column which is used in different data sources

[jozumhannes]

Hi guys,

before I had the problem that I wanted to find out in which data sources a column is used. That worked with following code:

proc sql;

Create table found as

Select * FROM dictionary.columns

Where upcase(name) = "MFLL";

quit;

Now i have the value "CM8". This value is used in the column "Code". The column "Code" is used in differenct data sources.

I want to find out all data sources where the value "CM8" appears. Do you have an idea for an simple code? I tried to expand my previous code but I dont get any result.

[ballardw]

That's an extract all of the data sets with the variable and then select from each where the variable has the value.

Likely not "simple" code but routine and tedious.

Your question is a subset of this one: https://communities.sas.com/t5/SAS-Programming/Finding-a-value-in-all-datasets-in-all-variables/m-p/...

In that case the question involved multiple variable names, you know the name of the variable, Code so should be simpler.

You really want to get used to thinking of variables when thinking of values in data sets. That is different than columns in reports or output documents.

[jozumhannes]

Im sorry, I didnt get that.

[jebjur]

This may not be the most elegant way, but you can use PROC SQL and query dictionary.columns to get the names of the datasets that contain the CODE variable (and library name if necessary), and also the number of data sets that have the variable, and write those values to macro variables. Then in a macro you can read each one and look for a specific value in the CODE variable. If the data set contains it, capture the data set name and write it out to a final data set.

data x1;
input code $;
cards;
AB1
CD2
EF3
;
run;

data x2;
input code $;
cards;
AB2
CD2
EF4
;
run;

data x3;
input code $;
cards;
AB1
CD3
EF3
;
run;

proc datasets lib=work;
delete find final;
run;

proc sql;
select count(memname) into :cnt from dictionary.columns
Where upcase(name) = "CODE";
select memname into :dsets1-:dsets%trim(&cnt)
from dictionary.columns
Where upcase(name) = "CODE";
select libname into :lib1-:lib%trim(&cnt)
from dictionary.columns
Where upcase(name) = "CODE";
quit;

%macro v_find;
%DO i=1 %TO &cnt;
DATA find;
set &&lib&i...&&dsets&i;
if code='CD2' then do;
dname="&&dsets&i";
output;
end;
run;

%let dsid=%sysfunc(open(work.find));
%let ret=%sysfunc(attrn(&dsid,any));
%let rc=%sysfunc(close(&dsid));

%if &ret GE 1 %then %do;
proc append base=final (keep=dname) data=find force;
run;
%end;
%end;

%mend;

%v_find;

[jozumhannes]

Hi, first of all thank you for helping me!
I tried this Code but it didnt work.
I have 3 values as output data.(X1, X2 and X3). I think they are unimportant

As a result I have two tables
once "Directory" and once a table with "Name", "Member Type", "File Size" and "Last modified". But these are not the data sources

[fja]

Hello!

You should find an output table called work.final with two rows containing the results 'X1' and 'X2' ...

This is what I get running jebjur's code in SAS on demand. ... and that is correct searching the sample tables for CODE='CD2', is it not?

And being with it: What is wrong with the solution ballardw has cited? ... besides that it uses iml which might be intimidating for a new user.

Cheers fja

[Kurt_Bremser]

See this:

proc sql noprint;
select catx(".",libname,memname) into :dslist separated by " "
from dictionary.columns
where upcase(name) = "CODE";
quit;

data want;
length dataset_name $41;
set
  &dslist.
  indsname=dsname
;
where code = "CM8";
dataset_name = dsname;
keep dataset_name;
run;

proc sort data=want nodupkey;
by dataset_name;
run;

Untested.

[yabwon]

I did small test and the code works well, if I may, few suggestions:

1) I would add a dummy dataset to be sure the &dslist. is not null:

data _;
  code=" ";
run;

2)  since the value is "CM8" I would add condition for type, to avoid selecting botch character and numeric variables,

3) I would also add selection of maximal length among selected variables,

4) (optionally) I doubt there will be more 1561 (65534/42) datasets, but "better safe than sorry" so I would go with a macro-array: dslist1, dslist2,...,dslistN

 data _;
  code=" ";
run;

proc sql noprint;
select 
  catx(".",libname,memname) 
  ,max(length)
into :dslist1-,                             /* make macro-array */
     :len                                   /* get max length */
from dictionary.columns
where upcase(name) = "CODE"
and type="char"                             /* condition for type */
;
%let n=&SQLobs;
quit;

%put &=N.;
%put &=dslist1.;
%put &=dslist2.;
%put dslist&N.=&&dslist&N.;
%put &=len.;

data want;
  length dataset_name $ 41 code $ &len.;
  set
    %macro _(n);
      %do n=1 %to &n.;
        &&dslist&n.
      %end;
    %mend;
    %_(&N.)                                 /* loop over macro array */

    indsname=dsname
  ;
  where code = "CM8";

  dataset_name = dsname;
  keep dataset_name;
run;

proc sort data=want nodupkey;
  by dataset_name;
run;

Bart

[Kurt_Bremser]

If there is a risk of too many datasets, I would create the code to a temporary file and %INCLUDE that.

[yabwon]

one more "safety vault" came into my mind, also adding:

&&dslist&n(keep=code)

would prevent "type mismatch" for other variables.

Taking all into account something like this macro wrapper:

%macro findVarVal(variable, value, type=char);

  %local n len;
  data _1;
    &variable.=" ";
  run;

  data _2;
    &variable.=.;
  run;

  proc sql noprint;
  select 
    catx(".",libname,memname) 
    ,max(length)
  into :dslist1-,
       :len
  from dictionary.columns
  where upcase(name) = %upcase("&variable.")
  and type="&type."
  ;
  %let n=&SQLobs;
  quit;
  /*
  %put &=N.;
  %put &=dslist1.;
  %put &=dslist2.;
  %put dslist&N.=&&dslist&N.;
  %put &=len.;
  */
  data want;
    length dataset_name $ 41 &variable. 
    %if &type.=char %then $; &len.
    ;
    set
        %do n=1 %to &n.;
          &&dslist&n.(keep=&variable.)
        %end;
      indsname=dsname
    ;
    %if &type.=num %then
      %do;
        where &variable. = &value.;
      %end;
    %if &type.=char %then
      %do;
        where &variable. = symget('value');
      %end;

    dataset_name = dsname;
    keep dataset_name;
  run;

  proc sort data=want nodupkey;
    by dataset_name;
  run;
%mend findVarVal;

%findVarVal(code, CM8)


%findVarVal(name, John)


%findVarVal(age, 14, type=num)

(not 100% bullet proof, but good enough)