SQL Macro Variables Overview
Learn about creating macro variables using the SQL procedure INTO clause, including examples for creating single and multiple macro variables, storing unique column values, and displaying user-defined macro variables.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
The SQL Procedure INTO Clause The INTO clause creates macro variables. General form of the SQL procedure INTO clause: SELECT col1, col2,. . . INTO :mvar1, :mvar2,... 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 proc sql select sum(total_retail_price) format=dollar8. into : total from orion.order_fact where year(order_date)=2007 quit quit; sql noprint; 2007 and order_type=3 3; %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 proc sql sql outobs=3 3 double; select total_retail_price, order_date format=mmddyy10. into :price1-:price3, :date1-:date3 from orion.order_fact where year(order_date)=2007 2007 order by total_retail_price desc; quit quit; title; %put Top 3 sales amounts: #1: &price1 #2: &price2 #3: &price3; %put Top 3 sales dates: #1: &date1 #2: &date2 #3: &date3; 4
The SQL Procedure INTO Clause The INTO clause can store the unique values of a specified column in a single macro variable. SELECT DISTINCTcol1, . . . INTO :mvarSEPARATED 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 proc sql select distinct country into :countries separated by ', ' from orion.customer; quit quit; %put Customer Countries: &Countries; sql noprint; 6
The SQL Procedure Example: Display all user-defined macro variables. proc proc sql select name, value from dictionary.macros where scope='GLOBAL' order by name; quit quit; sql; 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 proc sql select name into: vars separated by ' ' from dictionary.macros where scope='GLOBAL'; quit quit; %put &vars; sql noprint; 8
Example: Create a utility macro that deletes all user-defined macro variables. %macro %macro deletemymacvars deletemymacvars; proc sql noprint; select name into: vars separated by ' ' from dictionary.macros where scope='GLOBAL'; quit; %symdel &vars; %mend %mend deletemymacvars; 9
proc proc sql select name from dictionary.macros where scope="GLOBAL"; quit quit; sql; %deletemymacvars deletemymacvars proc proc sql select name from dictionary.macros dictionary.macros where where scope scope="GLOBAL"; quit quit; sql;
