DATA Step, Macro, Functions and more

Find the Dataset Name having particular observation

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 140
Accepted Solution

Find the Dataset Name having particular observation

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


Accepted Solutions
Solution
‎05-16-2012 03:53 AM
Super User
Posts: 9,687

Re: Find the Dataset Name having particular observation

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

View solution in original post


All Replies
Frequent Contributor
Posts: 140

Re: Find the Dataset Name having particular observation

actually i am having many dataset s like d1-d300

Super User
Posts: 5,091

Re: Find the Dataset Name having particular observation

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.

Frequent Contributor
Posts: 140

Re: Find the Dataset Name having particular observation


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

Solution
‎05-16-2012 03:53 AM
Super User
Posts: 9,687

Re: Find the Dataset Name having particular observation

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

Frequent Contributor
Posts: 140

Re: Find the Dataset Name having particular observation

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

Super User
Posts: 9,687

Re: Find the Dataset Name having particular observation

data all;

set d1-d300 indsname=inds;

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

run;

Contributor
Posts: 44

Re: Find the Dataset Name having particular observation

Verbose approach Smiley Happy:

%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;

Respected Advisor
Posts: 3,124

Re: Find the Dataset Name having particular observation

FWIW, Another Verbose approach using Hash():

%macro mumbai;

data want (drop=_Smiley Happy;

%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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 325 views
  • 0 likes
  • 5 in conversation