DATA Step, Macro, Functions and more

finding specific values or string in a library with multiple datasets

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 112
Accepted Solution

finding specific values or string in a library with multiple datasets

SAS gurus,

                I am trying to find accountno in a library with multiple datasets. I am unable to accomplish the task and I have put the code I used to build the logic.  Can someone please show me the direction?

1. i am getting all the accountno total in a macro as well accountno

2. get all the datasets I need to look into using dictionary.columns

3. use a macro to list individual dataset which contains either one or more accountno in them. If possible need to get all the accountno information (possibly using proc sql; select option instead of data _null_ in my code). I would like ideally to have the information of the dataset and all the observations pertaining to that accountno. I am failing in my third step. please help?

 

thanks,

 

 

/*get the total number of accounts from kumngo and save them in a macro accountno*/
proc sql;
select count(distinct(accountno)) into :tot
from kumngo;
select distinct "'"||strip(accountno)|| "'" into :accountno

         seperated by ","
from kumngo;
quit;


/*Get all the the dataset names from datasets using dicitionary.columns*/
PROC SQL NOPRINT;
SELECT DISTINCT(MEMNAME), COUNT(DISTINCT(MEMNAME))
INTO :ds_list SEPARATED BY '~', :ds_cnt
FROM dictionary.columns
WHERE LIBNAME = 'DATASETS'
ORDER BY MEMNAME;
create table datasets as
select distinct (memname)
from dictionary.tables
where libname = 'DATASETS'
QUIT;


/*search individual dataset to find the accountno and list that dataset*/
%macro check;
%if &sqlobs ne 0 %then %do;
%DO I = 1 %TO &DS_CNT;
%LET DS = %SCAN (&DS_LIST., &I., ~);
data _null_;
set datasets.&DS. ;
if accountno in (&accountno) then put" accountnos are in &DS.";
run;

%end;
%end;
%mend;

%check ;


Accepted Solutions
Solution
‎03-09-2018 11:50 PM
PROC Star
Posts: 2,308

Re: finding specific values or string in a library with multiple datasets

Something like this would be a lot faster.

proc sql noprint;
  select 'DATASETS.'||MEMNAME into :dslist separated by ' '
  from DICTIONARY.TABLES
  where LIBNAME = 'DATASETS';
quit;

data WANT;
  set &dslist.(keep=ACCOUNTNO) indsname=_DS;
  where ACCOUNTNO in (&accountno.); 
  DSNAME=_DS;
run;

Please modify for your needs.

 

 

 

View solution in original post


All Replies
Solution
‎03-09-2018 11:50 PM
PROC Star
Posts: 2,308

Re: finding specific values or string in a library with multiple datasets

Something like this would be a lot faster.

proc sql noprint;
  select 'DATASETS.'||MEMNAME into :dslist separated by ' '
  from DICTIONARY.TABLES
  where LIBNAME = 'DATASETS';
quit;

data WANT;
  set &dslist.(keep=ACCOUNTNO) indsname=_DS;
  where ACCOUNTNO in (&accountno.); 
  DSNAME=_DS;
run;

Please modify for your needs.

 

 

 

Frequent Contributor
Posts: 112

Re: finding specific values or string in a library with multiple datasets

Thanks ChrisNZ. This seems to be the right solution. I would let you if I come across any problems. I didn't know indsname= option exists with set option.

Thanks,

 

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 124 views
  • 0 likes
  • 2 in conversation