LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (February 2008, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Tue, 26 Feb 2008 15:13:58 -0500
Reply-To:     Mike Rhoads <RHOADSM1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Rhoads <RHOADSM1@WESTAT.COM>
Subject:      Re: list processing: SQL into list or call execute
In-Reply-To:  <2C6B65AAC3623140922DE580669C456A03B7AB14@LTA3VS001.ees.hhs.gov>
Content-Type: text/plain; charset="us-ascii"

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;


Back to: Top of message | Previous page | Main SAS-L page