topic Re: Find the Dataset Name having particular observation in SAS Programming

Find the Dataset Name having particular observation

sas_Forum — Tue, 15 May 2012 10:26:48 GMT

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

Re: Find the Dataset Name having particular observation

sas_Forum — Tue, 15 May 2012 11:05:29 GMT

actually i am having many dataset s like d1-d300

Re: Find the Dataset Name having particular observation

Astounding — Tue, 15 May 2012 14:05:03 GMT

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.

Re: Find the Dataset Name having particular observation

Ksharp — Tue, 15 May 2012 14:15:50 GMT

data all;

set d1-d300 indsname=inds;

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

run;

Re: Find the Dataset Name having particular observation

JasonDiVirgilio — Tue, 15 May 2012 14:34:37 GMT

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;

Re: Find the Dataset Name having particular observation

Haikuo — Tue, 15 May 2012 14:56:55 GMT

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

Re: Find the Dataset Name having particular observation

sas_Forum — Wed, 16 May 2012 06:53:07 GMT

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

Re: Find the Dataset Name having particular observation

Ksharp — Wed, 16 May 2012 07:53:49 GMT

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

Re: Find the Dataset Name having particular observation

sas_Forum — Wed, 16 May 2012 07:58:54 GMT

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