BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buddha_d
Pyrite | Level 9

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

2 REPLIES 2
ChrisNZ
Tourmaline | Level 20

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.

 

 

 

buddha_d
Pyrite | Level 9

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,

 

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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