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.
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.
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;
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
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.
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
If there is a risk of too many datasets, I would create the code to a temporary file and %INCLUDE that.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.