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
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
actually i am having many dataset s like d1-d300
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.
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
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
Actually kharp i should know the name of the dataset or can u create the dataset name to a macro variable
data all;
set d1-d300 indsname=inds;
if city_name ='Mumbai' then do; dsn=scan(inds,-1,'.');output;end;
run;
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;
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.