Find the Dataset Name having particular observation

[sas_Forum]

I will be having dataset names like D1 ,d2,d3 and and in this datasets i will have variable

name called city_name.

I want to find out the dataset that where the city_name is Mumbai

ex:

D1:
city_name
thane

D2:
city_name
Mumbai

D3:
city_name
dadar

Now i shd find that the city_name is in datset D2

[sas_Forum]

actually i am having many dataset s like d1-d300

[Astounding]

A little more information is needed.

Does each data set contain exactly one observation?

If a data set contains more than one observation, will CITY_NAME be the same or will it change from observation to observation?

Could more than one data set contain Mumbai?

Are all your data sets stored in the same library?

I think that would be enough to come up with an answer.

[sas_Forum]

The  Datset names are like Temp1,d2,d10,d30,d40,qer as i dont know the dataset names and the names wont be in serial as D1 to D20 some datasets my wont be there

[Ksharp · Accepted solution]

Querying dictionary table to get these table name.

proc sql noprint;
 select memname into : list separated by ' ' from dictionary.members where libname='WORK';
quit;
data want;
set &list indsname=inds;
if city_name ='Mumbai' then do; dsn=scan(inds,-1,'.');output;end;
run;

Ksharp

[sas_Forum]

Actually kharp i should know the name of the dataset or can u create the dataset name to a macro variable

[Ksharp]

data all;

set d1-d300 indsname=inds;

if city_name ='Mumbai' then do; dsn=scan(inds,-1,'.');output;end;

run;

[JasonDiVirgilio]

Verbose approach :

%macro loopallds(ds=, city=,n=);

      %do i = 1 %to &n;

        proc sql noprint;

          select count(*) into :dscount from d&i. where city_name="&city";

        quit;

        %if &dscount>=1 %then %let &ds=D&i;

      %end;

%mend;

**test data**;

data d1;

  length city_name $15.;

  city_name='London';

run;

data d2;

  length city_name $15.;

  city_name='Mumbai';

run;

data d3;

  length city_name $15.;

  city_name='Paris';

run;

%macro testit;

  %local dsn;

  %loopallds(ds=dsn, city=Mumbai,n=3);

  %put The dataset is=&dsn;

%mend;

%testit;

[Haikuo]

FWIW, Another Verbose approach using Hash():

%macro mumbai;

data want (drop=_:);

%do i=1 %to 3;

  set d&i(obs=1);

dcl hash h&i.(dataset:"d&i", multidata:'y');

h&i..definekey('city_name');

h&i..definedone();

do _rc=h&i..find(key:'Mumbai') by 0 while (_rc=0);

dsn="d&i";

output;

_rc=h&i..find_next(key:'Mumbai');

end;

%end;

run;

%mend;

%mumbai

Regards,

Haikuo