O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

where u.name=n.state)
where u.name in (select state from sql.newpop);
select Name format=$17., Capital format=$15.,
Population, Area, Continent format=$13., Statehood format=date9.
from work.unitedstates
;
Output 6.22 Sql.UnitedStates with Updated Population Data (Partial Output)
How It Works
The UPDATE statement updates values in the Sql.UnitedStates table (here with the alias
U). For each row in the Sql.UnitedStates table, the in-line view in the SET clause returns
a single value. For rows that have a corresponding row in Sql.NewPop, this value is the
value of the Population column from Sql.NewPop. For rows that do not have a
corresponding row in Sql.NewPop, this value is missing. In both cases, the returned
value is assigned to the Population column.
The WHERE clause ensures that only the rows in Sql.UnitedStates that have a
corresponding row in Sql.NewPop are updated by checking each value of Name against
the list of state names that is returned from the in-line view. Without the WHERE clause,
rows that do not have a corresponding row in Sql.NewPop would have their Population
values updated to missing.
Creating and Using Macro Variables
Problem
You want to create a separate data set for each unique value of a column.
Creating and Using Macro Variables 205
Background Information
The Sql.Features data set contains information about various geographical features
around the world.
libname sql 'SAS-library';
proc sql outobs=10;
title 'Features';
select Name format=$15., Type,Location format =$15.,Area,
Height, Depth, Length
from sql.features;
Output 6.23 Features (Partial Output)
Solution
To create a separate data set for each type of feature, you could go through the data set
manually to determine all the unique values of Type, and then write a separate DATA
step for each type (or a single DATA step with multiple OUTPUT statements). This
approach is labor-intensive, error-prone, and impractical for large data sets. The
following PROC SQL code counts the unique values of Type and puts each value in a
separate macro variable. The SAS macro that follows the PROC SQL code uses these
macro variables to create a SAS data set for each value. You do not need to know
beforehand how many unique values there are or what the values are.
proc sql noprint;
select count(distinct type)
into :n
from sql.features;
select distinct type
206 Chapter 6 Practical Problem-Solving with PROC SQL
into :type1 - :type%left(&n)
from sql.features;
quit;
%macro makeds;
%do i=1 %to &n;
data &&type&i (drop=type);
set sql.features;
if type="&&type&i";
run;
%end;
%mend makeds;
%makeds;
Log 6.1 SAS Log After Creating a Separate Data Set for Each Unique Value of a Column
240 proc sql noprint;
241 select count(distinct type)
242 into :n
243 from sql.features;
244 select distinct type
245 into :type1 - :type%left(&n)
246 from sql.features;
247 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds
248
249 %macro makeds;
250 %do i=1 %to &n;
251 data &&type&i (drop=type);
252 set sql.features;
253 if type="&&type&i";
254 run;
255 %end;
256 %mend makeds;
257 %makeds;
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.DESERT has 7 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 1.14 seconds
cpu time 0.41 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.ISLAND has 6 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.00 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.LAKE has 10 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 74 observations read from the data set SQL.FEATURES.
NOTE: The data set WORK.MOUNTAIN has 18 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
Creating and Using Macro Variables 207

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required