While there are certainly ways of doing this now, won't it be nice (when SAS 9.2 finally arrives) when we can just say:

DATA MyCollectionOfDataSets; set c1-c999;

Mike Rhoads Westat RhoadsM1@Westat.com

-----Original Message----- From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu] On Behalf Of Fehd, Ronald J. (CDC/CCHIS/NCPHI) Sent: Tuesday, February 26, 2008 9:43 AM To: ___SAS-L Subject: 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;