topic Re: check which data sets with name ttt are in work library and put the list on macro var in SAS Studio

check which data sets with name ttt are in work library and put the list on macro var

Ronein — Tue, 09 Aug 2022 08:25:03 GMT

Hello

I want to check which data sets with start 'ttt' are in WORK library and put them in a macro variable with separation by '+'.

In this case the required macro variable will get value ttt1+ttt2+ttt3.

What is the way to do it please?


Data ttt1;
input ID;
cards
;
2
;
run;

Data ttt2;
input ID;
cards
;
3
;
run;

Data ttt3;
input ID;
cards
;
4
;
run;

Data www;
input City $;
cards
;
Athens
;
run;

Re: check which data sets with name ttt are in work library and put the list on macro var

Kurt_Bremser — Tue, 09 Aug 2022 08:32:31 GMT

Use PROC SQL, and SELECT INTO with SEPARATED BY from DICTIONARY.TABLES.

Re: check which data sets with name ttt are in work library and put the list on macro var

Ronein — Tue, 09 Aug 2022 08:51:46 GMT

Thanks,

In the result I get also data sets that are not "ttt" , why??

Data ttt1;
input ID;
cards
;
2
;
run;

Data ttt2;
input ID;
cards
;
3
;
run;

Data ttt3;
input ID;
cards
;
4
;
run;

Data www;
input City $;
cards
;
Athens
;
run;


Proc sql noprint;
   select memname into : VVV separated by '+'
   from dictionary.tables 
   where compress(libname)='WORK' AND  compress(memname) like 'ttt%'
   ;
run;
%put &VVV;

Re: check which data sets with name ttt are in work library and put the list on macro var

Ronein — Tue, 09 Aug 2022 09:29:03 GMT

Strange,

this query work well

Proc sql noprint;
   select memname into : VVV separated by '+'
   from dictionary.tables 
   where UPCASE(libname)='WORK' 
			AND  UPCASE(memname) like 'TTT%'
   ;
run;
%put &VVV;

Re: check which data sets with name ttt are in work library and put the list on macro var

Kurt_Bremser — Tue, 09 Aug 2022 09:51:06 GMT

On a new SAS session, you will see this:

 102        Proc sql noprint;
 103           select memname into : VVV separated by '+'
 104           from dictionary.tables
 105           where compress(libname)='WORK' AND  compress(memname) like 'ttt%'
 106           ;
 NOTE: No rows were selected.
 107        run;
 NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
 108        %put &VVV;
 WARNING: Apparent symbolic reference VVV not resolved.

so I take it that your macro variable was already defined from a previous attempt.

Libnames and memnames are always uppercase in the DICTIONARY tables/views.