Macro variables, proc sql and the data dictionary

The efficiency quest

Objectives

Flexibility

Efficiency

Laziness

Examples of macro variable lists

“charval1”,”charval2”,”charval3”,….

4506005350205432, 3700120043210987, 2208543456709876

var1 var2 var3 var4 …..

&macrovarlist in proc and data step statements

1. where=(numvar in (&macrovarlist)) ;

2. where=(charvar in (&macrovarlist)) ;

3. proc whatever data=ds1 (where=(charvar in (&macrovarlist)));

4. proc whatever ; var &macrovarlist ;

5. set abc (keep = &macrovarlist) ;

6. where var in (&macrovarlist) ;

7. set &macrovarlist ;

Proc SQL features

select metadata from dictionary ;

select values into :macrovarlist separated by „delimiter‟ ;

The dictionary is created for each SAS session and updated dynamically.

Sashelp.vtables is a view on dictionary.tables

Scenario #1

Client sends e-mail with list of accounts to be matched then sorted. Steps

1. Paste the list into program editor and wrap with data step and datalines statement.

2. Create a macro variable list using proc sql.

3. Subset a large master data source to quickly extract the client‟s data.

A typical client request

Dear Analyst,

I need everything about these accounts:

4500396774285192

4500962650797314

4500296321583120

4500469120742973

As soon as possible.

Input values into dataset

data accts ;

input acct_num $16. ;

datalines ;

4501180048137018

4500600001486376

4500600002909772

4500607700775017

;

run ;

Create a quoted list

proc sql noprint ;

select quote(acct_char) into :acctlist

separated by ','

from cardlist ;

quit ;

%put &acctlist ;

"4500396774285192","4500962650797314","4500296321583120","4500469120742973"

Subset using list

data subset ;

set demodata.billrecs (where=(acct_char in (&acctlist))) ;

run ;

BUT

Most Databases do not like double quotes

Create a single quoted list

proc sql noprint ;

select "'"||left(trim(acct_char))||"'" into :acctlist1

separated by ','

from cardlist ;

quit ;

%put &acctlist1 ;

‘4500396774285192’,’4500962650797314’,’4500296321583120’,’4500469120742973’

Efficient DB subsetting

proc sql;

connect to db2 (ssid= dsn);

create table out2 as select * from connection to db2

(select * from db2table

where acct_char in (&acctlist1) );

disconnect from db2;

quit;

Scenario #2

Need to access data across range of libraries or datasets.

Create &macrovarlist from data dictionary based on a pattern.

Use &macrovarlist after a set statement in a data step.

Similar tables in a library

proc sql noprint ;

select 'SASHELP.'||left(memname)

into :memlist separated by ' '

from dictionary.members

where libname = 'SASHELP'

and memname like 'NVST%' ;

quit ;

%put &memlist ;

SASHELP.NVST1 SASHELP.NVST2 etc.

Create concatenation

data all_nvst ;

set &memlist ;

run ;

proc print data = all_nvst ;

run ;

Concatenation views

data all_nvst_v /view= all_nvst_v ;

set &memlist ;

run ;

Minimal creation time

Minimal storage

Dynamic

Dictionary data (v8) Proc sql SASHELP

dictionary.tables dictionary.columns dictionary.indexes dictionary.views dictionary.macros dictionary.extfiles dictionary.options dictionary.catalogs dictionary.titles

Sashelp.vtable Sashelp.vcolumn Sashelp.vindex Sashelp.vview Sashelp.vmacro Sashelp.vextfl Sashelp.vcolumn Sashelp.vcatalg Sashelp.vtitle

Additional dictionary data (v9) Proc sql SASHELP

dictionary.check_constraints

dictionary.constraint_column_usage

dictionary.constraint_table_usage

dictionary.dataitems

dictionary.destinations

dictionary.dictionaries

dictionary.engines

dictionary.filters

dictionary.functions

dictionary.goptions

dictionary.infomaps

dictionary.prompts

dictionary.promptsxml

dictionary.referential_constraints

dictionary.remember

dictionary.styles

dictionary.table_constraints

sashelp.vallopt

sashelp.vbplayrs

sashelp.vdatait

sashelp.vdctnry

sashelp.vdest

sashelp.vengine

sashelp.verbmgr

sashelp.vfilter

sashelp.vformat

sashelp.vfunc

sashelp.vgopt

sashelp.vidmsg

sashelp.vinfomp

sashelp.vlibnam

sashelp.vmember

sashelp.vprmxml

sashelp.vprompt

sashelp.vrefcon

sashelp.vrememb

sashelp.vtabcon

proc sql noprint;

select distinct cats

("describe table dictionary.",memname,";")

into :describeAll separated by ' '

from dictionary.dictionaries

;

&describeAll

quit;

Additional information

For some useful DB2 features see Harry Droogendyk‟s data driven coding paper:

http://www.stratia.ca/papers/QC_Using_Dictionary_Tables.pdf

Peter Eberhardt‟s extensive review of the SAS data dictionary

www2.sas.com/proceedings/sugi31/259-31.pdf

Explicit data step looping

Adds control over the SAS data step

Useful comparison with other languages

More logical sequence of SAS statements

Scenario #4

Need to create header and trailer on a flat file from SAS dataset.

Write header first, then formatted data, then trailer with totals.

Write trailer when do loop completes.

Controlling the loop

data _null_ ; put 'Header text' ; do until (last) ; set demodata.billrecs end = last ; put variables ; end ; put 'Trailer text' ; stop ; run ;

data _null_ ; file "!TMP\Example8_acctlist.txt" ; /* Target */ rundate = today() ; put @1 'List created on ' rundate yymmdd10. ; /* Header */ do until (last) ; /* Loop control */ set demodata.billrecs /* Source inside loop */ (where=(acct_char in (&acctlist))) /* Filter */ end = last ; /* Terminates loop */ n ++ 1 ; /* Counter */ put @1 acct_char $16. ; /* Output record */ end ; put @1 'Record count = ' @14 n z10. ; /* Trailer */ stop ; /* Good precaution */ run ;

Benefits

„Toolbox‟ utilities with many applications

Data driven programming

Flexibility

Accuracy

Automation

Thank you

Andrew Farrer

CIBC Business Intelligence Engineering

Andrew.Farrer@cibc.com