topic Re: Find a value from a column which is used in different data sources in SAS Enterprise Guide

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

jozumhannes — Wed, 10 May 2023 14:10:16 GMT

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.

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

ballardw — Wed, 10 May 2023 14:43:35 GMT

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/315651

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.

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

jozumhannes — Wed, 10 May 2023 17:48:28 GMT

Im sorry, I didnt get that.

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

jebjur — Wed, 10 May 2023 18:18:46 GMT

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;

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

jozumhannes — Wed, 10 May 2023 19:36:31 GMT

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

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

fja — Sun, 14 May 2023 09:03:48 GMT

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

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

Kurt_Bremser — Sun, 14 May 2023 09:50:57 GMT

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.

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

yabwon — Sun, 14 May 2023 13:01:17 GMT

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

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

Kurt_Bremser — Sun, 14 May 2023 13:06:37 GMT

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

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

yabwon — Sun, 14 May 2023 13:16:15 GMT

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)