tip: list processing: SQL into list or call execute

A user submitted a request for a list of data set names
to be used in a set statement:

DATA MyCollectionOfDataSets;
set c1 c2 c3 ... c999;

These two examples do the same thing:
make a list of data sets names of the form <Libref>.<Data Set Name>

Compare the times for each solution.
note that while SQL is faster is this RnD example
in a production environment where there may be many data sets in the
Libref
it may be slower.

options fullstimer;*show user and system real time and memory usage;

*parameters;
%Let Libref = sashelp;
%let DataPrefix = c;


*solution 1;
Proc SQL noprint;
select catt("&Libref..", MemName)
into :List separated by ' '
from Dictionary.Tables
where LibName eq "%upcase(&Libref.)"
and MemName like "%upcase(&DataPrefix.)%";
quit;

%Put _global_;

%symdel SQLOBS SQLOOPS SQLXOBS SQLRC;


*solution 2;
PROC Contents data = &Libref.._all_
noprint
out = Work.ListNames
(keep = MemName MemType
where = (MemType eq 'DATA'
and MemName like "%upcase(&DataPrefix.)%"));

%Let List =;*initialize;
DATA _Null_;
do until(last.MemName);
set Work.ListNames (drop = MemType)
end = EndoFile;
by MemName;
end;
call execute(catt('%nrstr(%let List = &List. &Libref..',MemName,''));
run;
%Put _global_;
run;

*usage:;
DATA List;
set &List.;

Ronald J. Fehd, IT Spec
HelpDesk Level 3: SAS

Ron Fehd the macro maven CDC Atlanta GA USA RJF2 at cdc dot gov

keywords: bricolage, creative tinkering, false endpoints

99 %Let Libref = sashelp;
100 %let DataPrefix = c;
101
102 Proc SQL noprint;
103 select catt("&Libref..", MemName)
104 into :List separated by ' '
105 from Dictionary.Tables
106 where LibName eq "%upcase(&Libref.)"
107 and MemName like "%upcase(&DataPrefix.)%";
108 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.03 seconds
user cpu time 0.00 seconds
system cpu time 0.03 seconds
Memory 133k

109
110 %Put List:&List.;
List:sashelp.CITIDAY sashelp.CITIMON sashelp.CITIQTR sashelp.CITIWK
sashelp.CITIYR sashelp.CLASS sashelp.CLNMSG sashelp.CNTAINER
sashelp.COLUMN sashelp.COMPANY sashelp.CSFDEFS sashelp.CTHEME
111
112 %symdel SQLOBS SQLOOPS LIST SQLXOBS SQLRC ;
113
114 PROC Contents data = &Libref.._all_
115 noprint
116 out = Work.ListNames
117 (keep = MemName MemType
118 where = (MemType eq 'DATA'
119 and MemName like "%upcase(&DataPrefix.)%"));
120
121 %Let List =;

NOTE: The data set WORK.LISTNAMES has 113 observations and 2 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.42 seconds
user cpu time 0.18 seconds
system cpu time 0.23 seconds
Memory 475k


122 DATA _Null_;
123 do until(last.MemName);
124 set Work.ListNames (drop = MemType)
125 end = EndoFile;
126 by MemName;
127 end;
128 call execute(catt('%nrstr(%let List = &List.
&Libref..',MemName,''));
129 run;

NOTE: There were 113 observations read from the data set WORK.LISTNAMES.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
Memory 202k

NOTE: CALL EXECUTE generated line.
1 + %let List = &List. &Libref..CITIDAY;
2 + %let List = &List. &Libref..CITIMON;
3 + %let List = &List. &Libref..CITIQTR;
4 + %let List = &List. &Libref..CITIWK;
5 + %let List = &List. &Libref..CITIYR;
6 + %let List = &List. &Libref..CLASS;
7 + %let List = &List. &Libref..CLNMSG;
8 + %let List = &List. &Libref..CNTAINER;
9 + %let List = &List. &Libref..COLUMN;
10 + %let List = &List. &Libref..COMPANY;
11 + %let List = &List. &Libref..CSFDEFS;
12 + %let List = &List. &Libref..CTHEME;
130 %Put _global_;
GLOBAL LIST sashelp.CITIDAY sashelp.CITIMON sashelp.CITIQTR
sashelp.CITIWK sashelp.CITIYR sashelp.CLASS sashelp.CLNMSG
sashelp.CNTAINER sashelp.COLUMN sashelp.COMPANY sashelp.CSFDEFS
sashelp.CTHEME
GLOBAL DATAPREFIX c
GLOBAL LIBREF sashelp
131 run;