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 ;
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.
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.
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,
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.