How To Use Proc SQL Select Into For List Processing SAS 1 HW06
User Manual: SAS-1
Open the PDF directly: View PDF .
Page Count: 16
Download | |
Open PDF In Browser | View PDF |
SESUG Proceedings (c) SESUG, Inc (http://www.sesug.org) The papers contained in the SESUG proceedings are the property of their authors, unless otherwise stated. Do not reprint without permission. SEGUG papers are distributed freely as a courtesy of the Institute for Advanced Analytics (http://analytics.ncsu.edu). Paper HW06 How To Use Proc SQL select into for List Processing Ronald J. Fehd, Centers for Disease Control and Prevention, Atlanta, GA, USA ABSTRACT The SAS R macro language is simple, yet powerful. List Processing with Proc SQL is also simple, yet powerful. This Hands On Workshop paper provides programmers with knowledge to use the Proc SQL select into clause with the various SQL dictionaries to replace macro arrays and %do loops. Expected audience is intermediate to advanced users, and macro programmers. Keywords: INTRODUCTION dynamic programming, list processing, macro, SQL. 1. How do I process every column in a dataset? 2. How do I process every file in a folder? 3. How do I process every member in a libref? or 4. How do I process every item in a list? In this paper I review the theory of programming, how to process every item in a list using the utter simplicity of Proc SQL select into :list processing with SQL’s dictionary tables. PREREQUISITES Students are expected to have the following minimum background: • programming experience: three to seven years • data step: data structure allocation with attribute or length statements • macro language: allocate macro variables write macros with one or more steps • procedures: TOPICS Contents, Print • programming theory: vocabulary • Proc SQL syntax • list processing (dynamic programming) with dictionaries: – columns – dictionaries, v9 – filenames, not an sql dictionary, read with scl functions – macros – options, v9: group – tables List Processing: Writing Macro Calls Contents Programming Theory 3 SQL syntax 3 2.1 ProcSQL-0-syntax.sas . . . . . . . . . . . . . . . . 3 9 Dictionary.Columns . . . . . . . . . . . . . . . . . . . . . 9 5.1 ProcSQL-describe-table-D-Columns.log snip 1 . . . 9 5.2 ProcSQL-describe-table-D-Columns.log snip 2 . . . 9 5.3 ProcSQL-select-into-list-from-D-Columns.sas . . . 10 Dictionary.Tables . . . . . . . . . . . . . . . . . . . . . . 10 SQL Basic Processes 4 5.4 ProcSQL-describe-table-D-Tables.log snip 1 . . . . 10 Listing Data Structure . . . . . . . . . . . . . . . . . . . . 4 5.5 ProcSQL-describe-table-D-Tables.log snip 2 . . . . 10 3.1 ProcSQL-1-describe-table-libref-data.sas . . . . . . 4 5.6 ProcDsn.sas Listing Data . . . . . . . . . . . . . . . . . . . . . . . . . 4 5.7 ProcSQL-select-into-list-from-D-Tables.sas . . . . . 11 ProcSQL-1-select-star.sas . . . . . . . . . . . . . . 4 Filenames . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Listing Subsets . . . . . . . . . . . . . . . . . . . . . . . 4 5.8 ProcSQL-select-into-list-from-filenames.sas snip 1 . 12 5.9 ProcSQL-select-into-list-from-filenames.sas snip 2 . 12 3.2 3.3 ProcSQL-1-select-subset.sas . . . . . . . . . . . . 4 Creating Data . . . . . . . . . . . . . . . . . . . . . . . . 4 3.4 ProcSQL-1-create-table-libref-data.sas . . . . . . . 4 Making Unique List . . . . . . . . . . . . . . . . . . . . . 4 3.5 ProcSQL-1-unique.sas . . . . . . . . . . . . . . . . 4 Summary of Basic SQL . . . . . . . . . . . . . . . . . . . 5 List Processing: Writing Constant Text 12 Suggested Readings . . . . . . . . . . . . . . . . . . . . 13 Bibliography . . . . . . . . . . . . . . . . . . . . . . . . . 13 5 4.1 ProcSQL-select-constant-text.sas . . . . . . . . . . 5 4.2 ProcSQL-select-constant-text.lst . . . . . . . . . . 5 4.3 ProcSQL-select-text-into-List.sas . . . . . . . . . . 5 4.4 ProcSQL-select-text-into-List.lst snip 1 . . . . . . . 5 4.5 ProcSQL-select-text-into-List.lst snip 2 . . . . . . . 5 List Processing Summary . . . . . . . . . . . . . . . . . . 6 Dictionary Dictionaries . . . . . . . . . . . . . . . . . . . 6 4.6 ProcSQL-describe-table-D-Dictionaries.sas . . . . . 6 4.7 ProcSQL-describe-table-D-Dictionaries.log . . . . . 6 4.8 ProcSQL-list-describe-table-dictionaries.lst . . . . . 7 4.9 ProcSQL-list-describe-table-dictionaries.sas . . . . 7 Dictionary Macros . . . . . . . . . . . . . . . . . . . . . . 7 4.10 Put-User.log Conclusion . . . . . . . . . . . . . . . . . . . . . 11 . . . . . . . . . . . . . . . . . . . . . 4.11 ProcSQL-describe-table-D-Macros.log 7 . . . . . . . 7 4.12 ProcSQL-order-D-Macros-log.sas . . . . . . . . . . 7 4.13 ProcSQL-order-D-Macros-log.log . . . . . . . . . . 8 4.14 ProcSQL-order-D-Macros-log.lst . . . . . . . . . . 8 Dictionary Options . . . . . . . . . . . . . . . . . . . . . . 8 4.15 ProcSQL-describe-table-D-Options.log . . . . . . . 8 4.16 ProcSQL-list-Options-define-value.sas . . . . . . . 8 4.17 ProcSQL-list-Options-define-value.lst snip 1 . . . . . 8 4.18 ProcSQL-list-Options-define-value.lst snip 2 . . . . . 8 4.19 ProcSQL-list-Options-define-value.log . . . . . . . 8 4.20 ProcSQL-list-groups.sas . . . . . . . . . . . . . . . 9 4.21 ProcSQL-list-groups.lst . . . . . . . . . . . . . . . 9 4.22 ProcSQL-list-groups.log . . . . . . . . . . . . . . . 9 2 pg. 2 of 16 PROGRAMMING THEORY We communicate in a natural language English (or Chinese, Dutch, French, or German) about the artificial language SAS. I use these computer science terms and concepts throughout this paper. program data structure data structure algorithm attributes organization algorithm input: process output: (also: metadata) declarative, information (compile-time) statements name : variable or column type : character or numeric length: in bytes character: 1–32,767 numeric : 1– 8 format label see Online Help: Index: declarative DATA step statements executable: action or control statements array: has numbered elements do I = 1 to dim(array-name); put array-name(I); list : has unnumbered items do over array-name; put array-name; drop, keep retain where libref, data, variable perform actions libref, data, to log or list SQL SYNTAX There are five SQL statements: 1 1. proc 2 3 2. create, closure (;): line 6 4 5 3. describe 6 7 4. select, closure (;): line 22 8 9 10 5. quit 11 The keyword select has one required clause, from, and five optional clauses: into, where, group by, having, and order by, which might be viewed more clearly conceptually in this hierarchy: 12 13 14 15 16 17 18 19 20 select 21 into from 22 23 ProcSQL-0-syntax.sas PROC SQL; PROC SQL noprint; create table table-name as query-expression>; describe table table-name <, ... table-name>; select object-item (object-item) <, ... object-item> into :macro-variable :macro-variable-A, :macro-variable-B :macro-variable1 - :macro-variable9999 from Libref.Data where ColumnChar eq ’value’ ColumnNum eq and ColumnChar2 eq ’value2’ group by group-by-item <, ... group-by-item>> having sql-expression order by order-by-item <, ... order-by-item>>; ; quit; where group by having order by 3 pg. 3 of 16 SQL BASIC PROCESSES Proc SQL can be used to do each of the basic processes: 1. list data structure 2. list data 3. list only subset 4. create data 5. unique LISTING DATA STRUCTURE Proc SQL works like Proc Contents. Instead of the data = option, SQL has the describe table statement. 1 ProcSQL-1-describe-table-libref-data.sas PROC Contents data = SAShelp.Class; 2 3 4 PROC SQL; describe table SAShelp.Class; quit; LISTING DATA Proc SQL works like Proc Print. In the Print method the object is referred to with the data = option. In SQL the object reference is the from clause. Star (asterisk: *) means all variables. 1 2 ProcSQL-1-select-star.sas PROC Print data = SAShelp.Class; var _all_; 3 4 PROC SQL; 5 6 select * /* _all_ */ from SAShelp.Class; quit; LISTING SUBSETS The where statement is available in all procedures. I illustrate it here as a data step option. The SQL select ... from statement has a where clause. 1 2 3 4 ProcSQL-1-select-subset.sas PROC Print data = SAShelp.Class (where = ( Sex eq ’F’ and Age ge 14)); var Name Age; 5 6 PROC SQL; 7 8 select from where Name, Age SAShelp.Class Sex eq ’F’ and Age ge 14; 9 quit; 10 CREATING DATA A common task is to copy a permanent data set from a permanent storage library to the work library. The SQL statement create table provides a similar data manipulation environment. 1 2 ProcSQL-1-create-table-libref-data.sas DATA Work.Class; set SAShelp.Class; 3 7 PROC SQL; create table Work.Class as select * from SAShelp.Class; quit; 1 PROC Sort 4 5 6 MAKING UNIQUE LIST The SQL select statement has a distinct function, which can be used to collapse many instances of variable values into a unique list. 2 3 4 data out (keep by ProcSQL-1-unique.sas = SAShelp.Class nodupkey = UniqueAge = Age); Age ; 5 6 PROC SQL; 7 8 9 4 create select from quit; table UniqueAge distinct Age SAShelp.Class as pg. 4 of 16 SUMMARY OF BASIC SQL There are several differences between the syntax of proc SQL and other procedures. The two most important to note are that column (variable) names are separated by commas, and dictionary tables’ values are upper case. select: use comma as delimiter between column names wrong: select Column1 Column2 Column3 right: select Column1, Column2, Column3 where: values in dictionary tables are upper case wrong: where Libname eq ’SAShelp’ right: where Libname eq ’SASHELP’ References: an introduction to SQL: Hermansen [21, sugi22.035] Ronk [27, sugi29.268] Wells [31, sugi26.105] Winn, Jr. [34, sugi22.067] LIST PROCESSING: WRITING CONSTANT TEXT The select statement accepts strings as one of its arguments; each string can be either single- or double-quoted, which allows the use of macro variables. 1 2 3 4 5 6 7 8 Note: the length of column MemName is 32, which accounts for the wide space between the words Class and has. 6 7 8 9 10 The above example selects four objects. Now let us concatenate text and variable value, using double bang (!!, two exclamation points) as the join operator and put that text into a macro variable. Note: line 9; The like operator chooses only names beginning with ’V’. Note: line 12; the statements in the macro variable are procedure statements, therefore they must be executed after the quit; statement in line 11. Statements in the macro variable List. 1 2 3 4 5 6 7 8 9 10 11 12 13 8 9 ProcSQL-select-constant-text.sas %Let Rows = rows; PROC SQL; select MemName, ’has’, Nobs, "&Rows." from Dictionary.Tables where LibName eq "SASHELP" and MemName eq "CLASS" and MemType eq "DATA"; quit; run; ProcSQL-select-constant-text.lst Number of Physical Member Name Observations --------------------------------------------------------CLASS has 17 rows ProcSQL-select-text-into-List.sas %Let List = *missing-;%* initialize for no rows selected; *PROC SQL noprint; PROC SQL; select ’Proc Contents data = SAShelp.’ !! trim(MemName) !! ’;’ into :List separated by ’ ’ from Dictionary.Tables where LibName eq "SASHELP" and MemName like "V%" and MemType eq "VIEW"; quit; &List.;%* execute statements in mvar List; run; ProcSQL-select-text-into-List.lst snip 1 Proc Contents data = SAShelp.VALLOPT; Proc Contents data = SAShelp.VCATALG; DiIorio and Abolafia [14, sugi29.237] discuss the SAShelp views associated with SQL dictionaries. Compare with program list-describe-table-dictionaries below, which lists SQL dictionaries. Output from statements in the macro variable List. 43 ProcSQL-select-text-into-List.lst snip 2 The CONTENTS Procedure 44 45 Data Set Name SASHELP.VALLOPT 5 Observations . pg. 5 of 16 LIST PROCESSING SUMMARY The are several steps in writing statements to a macro variable and executing them: 1. input: data structure (a) identify the input table (b) examine its data structure 2. process: (a) concatenation of text and values into macro variable i. identify subset, if any; note: values in ALL CAPS? ii. clarify the text surrounding the variable value(s): prefix, infix(es), suffix (b) remember closure or delimiter: clause(s): comma, space, other? statement(s): semicolon (;) step boundary: run; (c) execute the statements: SQL: SAS: procedures, macros: before quit; after quit; 3. output: consider ODS In the next sections I use this list processing check list to examine several of the more commonly used dictionaries. The first examples — dictionaries, macros and options — illustrate writing constant text. In the second section I show how to write macro calls to generate more complicated amounts of text when reading columns (variables), filenames, and tables. DICTIONARY DICTIONARIES Now let’s look at everything you ever wanted to know about all those SQL dictionaries. Statements to list the data structure: Note: V9 only. 1 2 3 Log with the data structure: 26 27 28 29 30 31 ProcSQL-describe-table-D-Dictionaries.sas PROC SQL; describe table Dictionary.Dictionaries; quit; run; ProcSQL-describe-table-D-Dictionaries.log create table DICTIONARY.DICTIONARIES ( memname char(32) label=’Member Name’, memlabel char(256) label=’Dataset Label’, name char(32) label=’Column Name’, type char(4) label=’Column Type’, 6 pg. 6 of 16 Dictionary.Dictionaries (new in V9) is unique on MemName + Name (Column Name). We need a data set (list) unique on MemName in order to write these statements: This program reads the SQL table Dictionary.Dictionaries, makes a data set with the names of all the SQL dictionaries, writes describe table statements for each, then executes those statements on line 10. The log contains the data structure of each SQL dictionary. 8 9 10 1 2 3 4 5 6 7 8 9 10 11 12 ProcSQL-list-describe-table-dictionaries.lst describe table Dictionary.CATALOGS; describe table Dictionary.CHECK_CONSTRAINTS; describe table Dictionary.COLUMNS; ProcSQL-list-describe-table-dictionaries.sas %*note: need v9 for D.Dictionaries; Proc SQL; create table List as select distinct MemName from Dictionary.Dictionaries; select ’describe table Dictionary.’ !! trim(MemName) !! ’;’ into :List separated by ’ ’ from List ; &List.; quit; run; Note line 10: the statements in the macro variable are SQL describe statements, therefore they must be executed before the quit; statement. Compare with program select-text-into-List above, which lists SAShelp views. This program illustrates a two-statement solution to this problem. See program ProcSQL-list-groups below for a single-statement solution. DICTIONARY MACROS The statement %Put user ; writes an unsorted list of macro variable names and values to the log. 22 23 24 25 26 27 28 Here is the data structure of Dictionary.Macros: 26 27 28 29 30 31 The task is to write a %put statement for each macro variable. Hard-coded this would be: %put a: &a.; %put b: &b.; %put z: &z.; 1 2 3 4 5 6 7 8 9 This program writes the put statements into the macro variable named list and executes them, line 13. 10 11 12 13 14 1 %Let 2 %Let 3 %Let 4 %Put GLOBAL Z end of GLOBAL A 1 GLOBAL B 22 Put-User.log A = 1; b = 22; z = end of list; _user_; list ProcSQL-describe-table-D-Macros.log create table DICTIONARY.MACROS ( scope char(32) label=’Macro Scope’, name char(32) label=’Macro Variable Name’, offset num label=’Offset into Macro Variable’, value char(200) label=’Macro Variable Value’ ProcSQL-order-D-Macros-log.sas Z = the last one; A = 1st item; M = middle; SQL; select ’%Put ’ !! trim(Name) !! ’: ’ !! trim(Value) !! ’;’ into :List separated by ’ ’ from Dictionary.Macros where Scope eq ’GLOBAL’ and not(Name like ’SQL%’) order by Name; quit; &List.; run; %Let %Let %Let Proc Note: line 13; the statements in the macro variable are SAS statements, therefore they must be executed after the quit; statement. 7 pg. 7 of 16 The statements written to the log are a sorted list of global macro variables. 43 44 45 46 The statements in macro variable List: 8 9 10 ProcSQL-order-D-Macros-log.log 13 &List.; A: 1st item M: middle Z: the last one ProcSQL-order-D-Macros-log.lst %Put A: 1st item; %Put M: middle; %Put Z: the last one; DICTIONARY OPTIONS Let’s take a look at Dictionary.Options. These next programs show how to find out: What is the data structure? • definitions and values of each option • what options are in each group 27 28 29 30 31 32 33 Note: group is available in v9. 34 Use list processing technique to write text: 1 2 3 4 5 6 7 8 9 Here’s the list of values and definitions of all options. The log is over 4600 lines long. 8 9 264 265 40 41 42 43 44 45 46 47 48 49 ProcSQL-describe-table-D-Options.log create table DICTIONARY.OPTIONS ( optname char(32) label=’Option Name’, opttype char(8) label=’Option type’, setting char(1024) label=’Option Setting’, optdesc char(160) label=’Option Description’, level char(8) label=’Option Location’, group char(32) label=’Option Group’ ProcSQL-list-Options-define-value.sas options linesize = max; PROC SQL; select ’Proc Options define value option = ’ !! trim(OptName) !! ’;’ into :List separated by ’ ’ from Dictionary.Options; quit; &List.; run; ProcSQL-list-Options-define-value.lst snip 1 Proc Options define value option = APPLETLOC; Proc Options define value option = ARMAGENT; ProcSQL-list-Options-define-value.lst snip 2 Proc Options define value option = MEMBLKSZ; Proc Options define value option = MEMCACHE; ProcSQL-list-Options-define-value.log Option Value Information For SAS Option APPLETLOC Option Value: C:\Program Files\SAS Institute\Shared Files\applets\9.1 Option Scope: SAS Session How option value set: Config File(s) Option Definition Information for SAS Option APPLETLOC Group= ENVFILES Group Description: SAS library and file location information Description: Location of Java applets Type: The option value is of type CHARACTER Maximum Number of Characters: 256 In program list-describe-table-dictionaries above, using the distinct function, we created a table of the unique dictionaries before we could write the text of the describe table statements. We can reduce two statements to one by creating a named column with the distinct values and then refer to the new column in our text concatenation. This trick requires that we make two macro variables, Item and List, for each of the columns: Group as Item, and text. 8 pg. 8 of 16 This example shows that the variable value can be used more than once, and there is no limit to how much text can be concatenated, either before or after use of the variable value. The calculated keyword indicates that the column Item has been created — distinct Group as Item — and is not in the the table being read: from Dictionary.Options. 1 2 3 4 5 6 7 8 9 10 11 12 Note that the column Item retains the label of Group. 6 7 8 9 10 11 12 13 The run; statement ensures that the the %Put Group: statement is written before each group. 40 41 ProcSQL-list-groups.sas %Let List = *no rows selected; Proc SQL; select distinct Group as Item, ’%Put Group: ’ !! calculated Item !! ’;Proc Options group = ’ !! calculated Item !! ’;run;’ into :Item, :List separated by ’ ’ from Dictionary.Options; quit; &List.; run; ProcSQL-list-groups.lst Option Group -----------------------------------------------------------------------COMMUNICATIONS %Put Group: COMMUNICATIONS ;Proc Options group = COMMUNICATIONS ;run; DATAQUALITY %Put Group: DATAQUALITY ;Proc Options group = DATAQUALITY ;run; ProcSQL-list-groups.log Group: COMMUNICATIONS SAS (r) Proprietary Software Release 9.1 TS1M3 42 43 NOAUTOSIGNON 44 45 COMAMID=TCP SAS/CONNECT remote submit will not automatically attempt to SIGNON Specifies the communication access method to be used LIST PROCESSING: WRITING MACRO CALLS In the following secions I provide program templates for processing the three most commonly used lists: columns (variables), tables (data set names), and files. DICTIONARY.COLUMNS The task is to process every column in a data set. 26 27 28 29 30 31 32 33 36 37 ProcSQL-describe-table-D-Columns.log snip 1 create table DICTIONARY.COLUMNS ( libname char(8) label=’Library Name’, memname char(32) label=’Member Name’, memtype char(8) label=’Member Type’, name char(32) label=’Column Name’, type char(4) label=’Column Type’, length num label=’Column Length’, ProcSQL-describe-table-D-Columns.log snip 2 label char(256) label=’Column Label’, format char(49) label=’Column Format’, 9 pg. 9 of 16 The primary parameter is the libref, line 1; see references in lines 22 and 27. The secondary parameter is the table name in the libref, lines 3–6, referred to in lines 23 and 28. 1 ProcSQL-select-into-list-from-D-Columns.sas %Let LibName = SAShelp; 2 3 4 5 6 %Let *Let *Let *Let MemName MemName MemName MemName = = = = Class; PrdSal2; PrdSal3; PrdSale; 7 A tertiary parameter, provided for clarity, is the member type, in (data, view), lines 8–9, see line 24. Do you want to review the statements generated? or is this program running in production? Choose to enable either of lines 11 or 12. 8 9 = data; = view; 10 11 12 %Let SQLprint = print;%*testing; *Let SQLprint = noprint; 13 14 15 16 17 The macro ProcVar is not yet defined at this time; this does not matter as we are writing the statements but have not yet executed them, line 44. %Let MemType *Let MemType 18 19 20 21 22 23 24 25 PROC SQL &SQLprint.; select ’%ProcVar(name=’ !! trim(Name) !! ’,type =’ !! trim(Type) !! ’,length=’ !! put(Length,5.) !! ’,format=’ !! trim(Format) !! ’,label =’ !! trim(Label) !! ’)’ into :List separated by ’ ’ from Dictionary.Columns where LibName eq "%upcase(&LibName.)" and MemName eq "%upcase(&MemName.)" and MemType eq "%upcase(&MemType.)"; quit; 26 Both global macro variables LibName and MemName are indirectly referenced via macro parameters libref and data as their defaults. 27 28 29 30 31 32 33 This is a simple processing example. Your own code goes here. 34 35 36 37 38 39 40 41 42 %Macro ProcVar(Libref = &LibName. /*global*/ ,Data = &MemName. /*global*/ ,Name = ,Type = ,Length = ,Format = ,Label = ); %If &Type. eq char %then %do; Proc Freq data = &Libref..&Data.; tables &Name.; %end; %Else %if &Type. eq num %then %do; Proc Univariate data = &Libref..&Data.; var &Name.; %end; title2 "&Libref..&Data..&Name. type: &Type. " "format: &Format. label: &Label."; run;%Mend; 43 Last, but not least, execute. 44 &List.;%*execute; DICTIONARY.TABLES In this example I show a production example of two programs where the first program containing the processing macro ProcDsn, contains a call (using the %include statement) of the second list processing program. This illustrates the concept of having one program define the parameters of another. The task is to process every data set in a library. What are the columns whose values are to be passed to the processing macro? 27 28 29 30 31 32 37 38 39 ProcSQL-describe-table-D-Tables.log snip 1 create table DICTIONARY.TABLES ( libname char(8) label=’Library Name’, memname char(32) label=’Member Name’, memtype char(8) label=’Member Type’, dbms_memtype char(32) label=’DBMS Member Type’, ProcSQL-describe-table-D-Tables.log snip 2 nobs num label=’Number of Physical Observations’, obslen num label=’Observation Length’, nvar num label=’Number of Variables’, 10 pg. 10 of 16 The primary parameter is the libref, line 1. 1 ProcDsn.sas %Let LibName = SAShelp;%*testing; 2 3 Do you want to subset the processing by choosing the member type before writing the statement? Enable either line 6 or 7. 4 %*Libname MyLib ’???’; %*Let LibName = MyLib;%*production; 5 6 7 %Let Op_MemType = eq ’data’; %*Let Op_MemType = in(’catalog’ ’data’ ’view’); 8 9 Do you want to review the statements generated? or is this program running in production? Choose to enable any or all of the testing statements in lines 11–13. 11 12 13 %Let SQLprint = print;%*testing; %*options source2;%*testing: echo include?; %*Put Op_MemType<%upcase(&Op_MemType.)>;%*testing; 14 15 As in the processing of Dictionary.Columns program above, the statements written include most of the list attributes to be passed to the macro. %Let SQLprint = noprint;%*production; 10 16 17 18 19 20 21 %Macro ProcDsn(Libref ,Data ,Type ,Label ,Nobs ,Nvar ,TitleN = &LibName. /*global*/ = = = = = = 2); 22 23 24 This is a simple processing example. Your own code goes here. 25 26 27 28 29 30 31 32 33 Call the list processing program that will read all tables in the library defined here and execute the macro ProcDsn defined in this program. Note that you could have other programs defining the macro ProcDsn and relying on the subroutine select-from-D-Tables. Compare with program select-from-D-Columns above. 34 35 36 37 38 39 40 %If &Type. eq CATALOG %then %do; %Put &Libref..&Data. type: &Type.; Proc Catalog catalog = &Libref..&Data.; contents; quit; %end; %Else %if &Type. eq DATA %then %do; %Put &Libref..&Data. nobs: &Nobs. nvar: &Nvar.; Proc SQL; describe table &Libref..&Data.; %end; %Else %if &Type. eq VIEW %then %do; Proc Contents data = &Libref..&Data.; title2&TitleN. "&Libref..&Data. type: &Type. " "nobs: &Nobs. nvar: &Nvar."; %end; %Else %Put &Libref..&Data. type unknown: &type.; run; %Mend; 41 42 1 %Include ’ProcSQL-select-into-list-from-D-Tables.sas’; ProcSQL-select-into-list-from-D-Tables.sas %*note: parameters assigned by calling program; 2 3 4 5 6 %*Let %*Let %*Let %*Inc LibName = SAShelp; Op_MemType = eq ’data’; SQLprint = print;%*testing; ’MacroProcessDsn’; %*testing; 7 8 %Let List = *empty: no rows selected; 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 PROC SQL &SQLprint.; select ’%ProcDsn(data=’ !! trim(MemName) !! ’,type =’ !! trim(MemType) !! ’,label=’ !! trim(MemLabel) !! ’,nobs =’ !! compress(put(Nobs,32.)) !! ’,nvar =’ !! compress(put(Nvar,32.)) !! ’)’ into :List separated by ’ ’ from Dictionary.Tables where LibName eq "%upcase(&LibName.)" and MemType %upcase(&Op_MemType.); quit; &List.;%*execute macro calls; %symdel Libname List Op_MemType SQLprint; 11 pg. 11 of 16 FILENAMES Processing a list of filenames is different from the previous examples as there is no SQL dictionary of folders and filenames. Reading the list of files using SQL requires some interesting tricks. In this example, I have polished a program written by Hamilton [20, sugi31.046] The primary parameter is the folder name. 36 37 38 39 %Let *Let *Let *Let ProcSQL-select-into-list-from-filenames.sas snip 1 Folder = c:\; Folder = .;%*here; Folder = %sysget(sasroot);%*directory-spec; Folder = %sysget(sasroot)\core\sasexe;%*922 files; 40 41 42 The Digits data set is used to make a larger data set, FileNmbrs, containing the file-numbers used by the dread function. 43 44 45 Two select statments are required: the first to assign a fileref, open the folder and read the number of files present. 48 49 50 51 52 65 66 67 68 69 70 71 72 The second statement writes the macro calls. 73 74 75 Line 76 generates the list of file numbers. Data Digits; length Digit 4; do Digit = 0 to 9; output; end; stop; run; 46 47 Omitted lines 53–64 contain allocations of macro variables E3 and E4 which are necessary for processing lists of 999 and 9999 files. %Let SQLprint = noprint;%*production; %Let SQLprint = print;%*testing; 76 77 78 %* see: from (&&E&Evalue.); %Let E1 = select ones.digit as as %Let E2 = select ones.digit + as from digits as FileNmbr from digits ones; 10 * tens.digit FileNmbr ones, digits as tens; ProcSQL-select-into-list-from-filenames.sas snip 2 Proc SQL &SQLprint.; select filename(’DirSpec’, "&Folder."), dopen(’DirSpec’) as Dir_id, dnum(calculated Dir_id) into :FileName_Rc, :Dir_id, :NmbrFiles from Digits where Digit = 0; %Let NmbrFiles = &NmbrFiles.;%*trim; %Let Evalue = %length(&NmbrFiles.); select ’%ProcFile(filename=’ !! dread(&Dir_id., FileNmbr) !! ’)’ into :List separated by ’ ’ from (&&E&Evalue.) where FileNmbr between 1 and &NmbrFiles; quit; %Let FileName_Rc = dclose(&Dir_id.); 79 Your filename-processing statements go here. 80 81 82 83 84 85 %Macro ProcFile( directory = &Folder. /*global*/ , filename = ); %if %index(&Filename.,.) %then %put filename.ext:<&FileName.>; %else %put other<&FileName.>; run; %mend; 86 After executing the macro calls then do housecleaning: symbol-delete all the macro variables used. CONCLUSION 87 88 89 90 &List.; %symdel E1 E2 E3 E4 Evalue Folder List SQLprint FileName_Rc Dir_id NmbrFiles; Any data set is a candidate for use by list processing. To produce dynamic programs follow these simple steps: • identify the data set (table) • examine its data structure • identify the variables (columns) that contain parameter values • develop a program with example code • use proc sql to write that code as text or macro call, substituting variable names for values • sit back and watch the log zoom by 12 pg. 12 of 16 ACKNOWLEDGEMENTS Ian Whitlock and Sig Hermansen have piqued my interest in SQL over the years with their contributions to SAS-L. Toby Dunn provided commentary on numerous examples. SUGGESTED READINGS bookshelf Carpenter [6, saspress.59224], Celko.2005] basics Dickstein and Pass [12, sugi29.269], Hu [22, sugi29.042], Lund [25, sugi30.257], Ronk [27, sugi29.268], Wells [31, sugi26.105], Winn, Jr. [34, sugi22.067] intermediate and advanced concepts: Barber [4, sugi22.198], Hamilton [20, sugi31.046] Hermansen [21, sugi22.035] Lafler [23, sugi28.019], Loren and Nelson [24, sugi23.031], Winn, Jr. [35, sugi23.035] list processing Abolafia [1, sugi30.031], Andrews [3, sugi31.039], Beakley and McCoy [5, sugi29.078], ch. 9, dynamic programming, Carpenter [6, saspress.59224], Carpenter [7, sugi30.028], Fehd [17, sgf2007.028], Fehd and Carpenter [18, sgf2007.113], Pollack [26, sugi30.057], Varney [30, sugi31.045], macros and SQL Adams [2, sugi28.087], Chiu and Heaton [10, sugi28.097], Delaney and Carpenter [11, sugi29.128], DiIorio and Abolafia [14, sugi29.237], DiIorio [13, sugi30.268], Droogendyk and Fecht [15, sugi31.251], Fehd [16, sugi29.070], First and Ronk [19, sugi31.107], Stroupe [28, sugi28.056], Sun and Wong [29, sugi30.040], Whitlock [32, sugi29.244], Whitlock [33, sugi30.252] Celko [8, Celko.2000], and Celko [9, BIBLIOGRAPHY [1] Jeff Abolafia. What would I do without proc SQL and the macro language? In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/03130.pdf. Coders’ Corner, 5 pp.; creating and using arrays of macro variables, creating partial sas statements using catx function. [2] John H. Adams. The power of recursive sas macros — how can a simple macro do so much? In Proceedings of the 28th SAS User Group International Conference, 2003. URL http://www2.sas.com/proceedings/ sugi28/087-28.pdf. Coders’ Corner, 5 pp.; reading directories and subdirectories using recursive macro calls. [3] Rick Andrews. Sas macro dynamics — from simple basics to powerful invocations. In Proceedings of the 31st SAS User Group International Conference, 2006. URL http://www2.sas.com/proceedings/sugi28/ 087-28.pdf. Coders’ Corner, 6 pp.; creating macro variables, comparison of SQL and call symput, making and using lists of values in macro variables, writing to file then including, using call execute for list processing, creating and using array of macro variables, bibliography. [4] Brenda M. Barber. Overcoming kainophobia: Replacing complex merges with proc sql. In Proceedings of the 22nd SAS User Group International Conference, 1997. URL http://www2.sas.com/proceedings/ sugi22/POSTERS/PAPER198.PDF. Poster, 6 pp.; merging using SQL joins. [5] Steven Beakley and Suzanne McCoy. Dynamic sas programming techniques, or how not to create job security. In Proceedings of the 29th SAS User Group International Conference, 2004. URL http://www2.sas.com/ proceedings/sugi29/078-29.pdf. Coders’ Corner, 10 pp.; using SQL to make lists of values, example programs. [6] Arthur L. Carpenter. Carpenter’s Complete Guide to the SAS Macro Language, Second Edition. Cary, NC: SAS Institute Inc., 2004. URL http://support.sas.com/publishing/bbu/companion_site/59224. html. 13 chap., 475 pp., appendices: 5, glossary: 3 pp., bibliography: 19 pp., index: 13 pp. [7] Arthur L. Carpenter. Storing and using a list of values in a macro variable. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/02830.pdf. Coders’ Corner, 6 pp.; making macro arrays using symputx or sql, iterating macro arrays, using scl functions, bibliography. 13 [8] Joe Celko. Joe Celko’s SQL for Smarties: Advanced SQL Programming, Second Edition. Morgan-Kaufmann, San Francisco, CA, USA, 2000. URL http://www.celko.com. [9] Joe Celko. Joe Celko’s SQL Programming Style. Elsevier, San Francisco, CA, USA, 2005. URL http: //www.elsevier.com. 10 chap., 216 pp., bibliography: 3 pp., index: 16 pp. [10] Grace Chiu and Edward Heaton. An efficient approach to combine SAS data sets with voluminous variables that need name and other changes. In Proceedings of the 28th SAS User Group International Conference, 2002. URL http://www2.sas.com/proceedings/sugi28/097-28.pdf. Coders’ Corner, 5 pp.; renaming variables, proc contents and proc sql. [11] Kevin P. Delaney and Arthur L. Carpenter. SAS macro: Symbols of frustration? %Let us help! a guide to debugging macros. In Proceedings of the 29th SAS User Group International Conference, 2002. URL http: //www2.sas.com/proceedings/sugi29/128-29.pdf. Hands-on Workshops, 20 pp.; using options to aid debugging, scan and qscan functions, problems when using single or double quotes, SQL selecting values into macro variable. [12] Craig Dickstein and Ray Pass. Data step vs. proc sql: What’s a neophyte to do? In Proceedings of the 26th SAS User Group International Conference, 2001. URL http://www2.sas.com/proceedings/sugi29/ 269-29.pdf. Beginning Tutorials, 9 pp.; comparison of merge and joins, using proc SQL for recoding, SQL functions. [13] Frank DiIorio. Rules for tools — the SAS utility primer. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/268-30.pdf. Tutorials, 19 pp.; reusing macros and programs, sql dictionary tables. [14] Frank DiIorio and Jeff Abolafia. Dictionary tables and views: Essential tools for serious applications. In Proceedings of the 29th SAS User Group International Conference, 2002. URL http://www2.sas.com/ proceedings/sugi29/237-29.pdf. Tutorials, 19 pp.; comparison of data structure of dictionary tables and sashelp views, review of differences between v6, v8, and v9, 12 examples. [15] Harry Droogendyk and Marje Fecht. Demystifying the SAS macro facility — by example. In Proceedings of the 31st SAS User Group International Conference, 2006. URL http://www2.sas.com/proceedings/ sugi31/251-31.pdf. tutorials, 15 pp.; list processing, macro functions: symdel, symexist, syslput, sysrput; proc sql, scl functions: open, attrn, close. [16] Ronald Fehd. Array: Construction and usage of arrays of macro variables. In Proceedings of the 29th SAS User Group International Conference, 2002. URL http://www2.sas.com/proceedings/sugi29/07029.pdf. Coders’ Corner, 6 pp.; using proc sql, bibliography. [17] Ronald J. Fehd. Journeymen’s tools: Data review macro FreqAll – using Proc SQL list processing with Dictionary.Columns to eliminate macro do loops. In Proceedings of the SAS Global Forum, 2007. URL http://www2.sas.com/proceedings/forum2007/028-2007.pdf. Coder’s Corner, 10 pp.; attributes, dictionary.columns, metadata, proc append, proc freq, proc sql, program header; bibliography. [18] Ronald J. Fehd and Art Carpenter. List processing basics: Creating and using lists of macro variables. In Proceedings of the SAS Global Forum, 2007. URL http://www2.sas.com/proceedings/forum2007/ 113-2007.pdf. Hands On Workshop, 20 pp.; comparison of methods: making and iterating macro arrays, scanning macro variable, writing calls to macro variable, write to file then include, call execute; 11 examples, bibliography. [19] Steven First and Katie Ronk. Intermediate and advanced SAS macros. In Proceedings of the 31st SAS User Group International Conference, 2006. URL http://www2.sas.com/proceedings/sugi31/10731.pdf. Hands-on Workshops, 16 pp.; call routines: execute, symdel, symput, symputn, symputx; functions: resolve, symexist, symget, symgetn, symglobal, symlocal, sysexec, sysget; macro arrays, proc sql, sas/connect: syslput, sysrput. [20] Jack Hamilton. Digits and dates: The SQL procedure goes loopy. In Proceedings of the 31st SAS User Group International Conference, 2006. URL http://www2.sas.com/proceedings/sugi26/061-26. pdf. Coders’ Corner, 11 pp.; simulating looping thru dates, using scl functions in sql, using date functions in sql. 14 [21] Sigurd Hermansen. Ten good reasons to learn SAS software’s SQL procedure. In Proceedings of the 22nd SAS User Group International Conference, 1997. URL http://www2.sas.com/proceedings/sugi26/06126.pdf. Advanced Tutorials, 5 pp.; comparison of SQL to data step. [22] Weiming Hu. Top ten reasons to use proc sql. In Proceedings of the 29th SAS User Group International Conference, 2004. URL http://www2.sas.com/proceedings/sugi29/042-29.pdf. Coders’ Corner, 6 pp.; joining, selecting values into list, text wrapping, summary functions, matching, inserting, using coalesce function, summarizing, fuzzy merges, examples, bibliography. [23] Kirk Paul Lafler. Undocumented and hard-to-find SQL features. In Proceedings of the 28th SAS User Group International Conference, 2003. URL http://www2.sas.com/proceedings/sugi28/019-28.pdf. Advanced Tutorials, 6 pp.; case logic, coalesce function, SQL statement options method and tree, SQL dictionary tables, automatic macro variables, performance issues, examples. [24] Judy Loren and Gregory S. Barnes Nelson. Sql step by step: An advanced tutorial for business users. In Proceedings of the 23nd SAS User Group International Conference, 1998. URL http://www2.sas.com/ proceedings/sugi23/Advtutor/p31.pdf. Advanced Tutorial, 10 pp.; examples, bibliography. [25] Pete Lund. An introduction to SQL in SAS. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/257-30.pdf. Tutorials, 22 pp.; basic syntax, where clause operators: null, missing, between, contains, like, sounds like; aggregating functions. [26] Stuart Pollack. Techniques for effectively selecting groups of variables. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/05730.pdf. Coders’ Corner, 4 pp.;. [27] Katie Minten Ronk. Introduction to proc sql. In Proceedings of the 29th SAS User Group International Conference, 2004. URL http://www2.sas.com/proceedings/sugi29/268-29.pdf. Tutorials, 13 pp.; examples. [28] Jane Stroupe. Nine steps to get started using SAS macros. In Proceedings of the 28th SAS User Group International Conference, 2003. URL http://www2.sas.com/proceedings/sugi28/056-28.pdf. Beginning Tutorials, 5 pp.; creating macro variables using proc sql, developing macros from programs. [29] Helen Sun and Cindy Wong. A macro for importing multiple excel worksheets into SAS data sets. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/ proceedings/sugi30/040-30.pdf. Coders’ Corner, 9 pp.; application: import .xls, using scan function in macro do loops, bibliography. [30] Brian Varney. Using metadata and project data for data-driven programming. In Proceedings of the 31st SAS User Group International Conference, 2006. URL http://www2.sas.com/proceedings/sugi31/04531.pdf. Coders’ Corner, 10 pp.; assertions, call execute, call symput, macro arrays, scl functions, SQL dictionary tables, stored processes, subsetting, write to file and %include. [31] Clayton Wells. Tips for using proc SQL to extract information from medical claims. In Proceedings of the 26th SAS User Group International Conference, 2001. URL http://www2.sas.com/proceedings/sugi26/ p105-26.pdf. Coder’s Corner, 4 pp.; comparison of proc transpose to sql, practical usage, SQL functions. [32] Ian Whitlock. A second look at SAS macro design issues. In Proceedings of the 29th SAS User Group International Conference, 2004. URL http://www2.sas.com/proceedings/sugi26/244-29.pdf. Tutorials, 18 pp.; examples, list processing, macro arrays, macro parameter naming conventions, sashelp views, SQL dictionary tables. [33] Ian Whitlock. Macro bugs: How to create, avoid, and destroy them. In Proceedings of the 30th SAS User Group International Conference, 2005. URL http://www2.sas.com/proceedings/sugi30/252-30. pdf. Tutorials, 20 pp.; call execute, call symput, debugging, design, how macro facility works, options used when testing: mfile mlogic mprint, readability, single and double quoting, scope of macro variables, testing, understanding difference between macro and sas bugs writing messages to log with %put. 15 [34] Thomas J. Winn, Jr. Introduction to using proc sql. In Proceedings of the 22nd SAS User Group International Conference, 1997. URL http://www2.sas.com/proceedings/sugi26/p105-26.pdf. Beginning Tutorial, 7 pp.; create tables and views, examples, joins, operators. [35] Thomas J. Winn, Jr. Intermediate proc sql. In Proceedings of the 23nd SAS User Group International Conference, 1998. URL http://www2.sas.com/proceedings/sugi23/Advtutor/p35.pdf. Advanced Tutorial, 7 pp.; examples, summary functions, views. Author: Ronald Fehd mailto:RJF2@cdc.gov Centers for Disease Control 4770 Buford Hwy NE Atlanta GA 30341-3724 To get the code examples in this paper search http://www.sascommunity.org for the HOW SQL for List Processing zip. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. R indicates USA registration. education: experience: SAS-L: Document Production: This paper was typeset in LATEX. For further information about using LATEX to write your SUG paper, consult the SAS-L archives: about the author: B.S. Computer Science, U/Hawaii, 1986 SUGI attendee since 1989 SAS-L reader since 1994 programmer: 20+ years data manager at CDC, using SAS: 18+ years author: 12+ SUG papers author: 4,000+ messages to SAS-L since1997 Most Valuable SAS-L contributor: 2001, 2003 http://www.listserv.uga.edu/cgi-bin/wa?S1=sas-l Search for : The subject is or contains: LaTeX The author’s address : RJF2 Since : 01 June 2003 16 pg. 16 of 16
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.4 Linearized : No Has XFA : No XMP Toolkit : Adobe XMP Core 4.2.1-c041 52.342996, 2008/05/07-21:37:19 Format : application/pdf Creator : Ronald J. Fehd Description : list processing Title : How To Use Proc SQL select into for List Processing Create Date : 2007:09:14 14:12:20-04:00 Creator Tool : LaTeX with hyperref package Modify Date : 2008:12:02 08:14:49-05:00 Metadata Date : 2008:12:02 08:14:49-05:00 Keywords : dynamic programming, list processing, macro, SQL Producer : pdfeTeX-1.11a Document ID : uuid:51514723-67c4-7b4b-8d85-20cf27b26b38 Instance ID : uuid:691caf70-1c1b-1f4b-b5f2-6bcca123aee7 Page Mode : UseNone Page Count : 16 Author : Ronald J. Fehd Subject : list processingEXIF Metadata provided by EXIF.tools