Creating Macro Variables in SQL Review 1 The

Creating Macro Variables in SQL (Review) 1

The SQL Procedure INTO Clause The INTO clause creates macro variables. General form of the SQL procedure INTO clause: SELECT col 1, col 2, . . . INTO : mvar 1, : mvar 2, . . . FROM table-expression WHERE where-expression other clauses; This form of the INTO clause does not trim leading or trailing blanks. 2

The SQL Procedure INTO Clause Example: Create a macro variable that contains the total price of all 2007 Internet orders. proc sql noprint; select sum(total_retail_price) format=dollar 8. into : total from orion. order_fact where year(order_date)=2007 and order_type=3; quit; %put Total sales: &total; 3

The INTO clause can create multiple macro variables. Example: Create macro variables with the date and amount of the top three sales from 2007. title 'Top 2007 Sales'; proc sql outobs=3 double; select total_retail_price, order_date format=mmddyy 10. into : price 1 -: price 3, : date 1 -: date 3 from orion. order_fact where year(order_date)=2007 order by total_retail_price desc; quit; title; %put Top 3 sales amounts: #1: &price 1 #2: &price 2 #3: &price 3; %put Top 3 sales dates: #1: &date 1 #2: &date 2 #3: &date 3; 4

The SQL Procedure INTO Clause The INTO clause can store the unique values of a specified column in a single macro variable. SELECT DISTINCT col 1, . . . INTO : mvar SEPARATED BY 'delimiter', . . . FROM table-expression WHERE where-expression other clauses; 5

The SQL Procedure INTO Clause Example: Create a macro variable with a list of all customer countries. Delimit the country codes with a comma and space. proc sql noprint; select distinct country into : countries separated by ', ' from orion. customer; quit; %put Customer Countries: &Countries; 6

The SQL Procedure Example: Display all user-defined macro variables. proc sql; select name, value from dictionary. macros where scope='GLOBAL' order by name; quit; 7

The SQL Procedure INTO Clause Example: Create a macro variable with a list of all user-defined macro variable names. Delimit the names with spaces. proc sql noprint; select name into: vars separated by ' ' from dictionary. macros where scope='GLOBAL'; quit; %put &vars; 8

Example: Create a utility macro that deletes all user-defined macro variables. %macro deletemymacvars; proc sql noprint; select name into: vars separated by ' ' from dictionary. macros where scope='GLOBAL'; quit; %symdel &vars; %mend deletemymacvars; 9

proc sql; select name from dictionary. macros where scope="GLOBAL"; quit; %deletemymacvars proc sql; select name from dictionary. macros where scope="GLOBAL"; quit;