Creating New Columns

Adding Text to Output

You can add text to the output by including a string expression, or literal expression, in a query. The following query on table PostalCodes includes two strings as additional columns in the output:

proc sql outobs=12;
   title1 'U.S. Postal Codes';
   title2 'First 12 Rows Only';
   select 'Postal code for', Name, 'is', Code
      from postalcodes;
quit;

Adding Text to Output

To prevent the column headings Name and Code from printing, you can assign a label that starts with a special character to each of the columns. PROC SQL does not write the column name when a label is assigned, and it does not write labels that begin with special characters. For example, you could use the following query to suppress the column headings that PROC SQL displayed in the previous example:

proc sql outobs=12;
   title1 'U.S. Postal Codes';
   title2 'First 12 Rows Only';
   select 'Postal code for', Name label='#', 'is', Code label='#'
      from postalcodes;
quit;

Suppressing Column Headings in Output

Calculating Values

You can perform calculations with values that you retrieve from numeric columns. The following example converts temperatures in the WorldTemps table from Fahrenheit to Celsius:

proc sql outobs=12;
   title1 'Low Temperatures in Celsius';
   title2 'First 12 Rows Only';
   select City, (AvgLow - 32) * 5/9 format=4.1
      from worldtemps;
quit;

Calculating Values

Assigning a Column Alias

By specifying a column alias, you can assign a new name to any column within a PROC SQL query. The new name must follow the rules for SAS names. The name persists only for that query.

When you use an alias to name a column, you can use the alias to reference the column later in the query. PROC SQL uses the alias as the column heading in output. The following example assigns an alias of LowCelsius to the calculated column from the previous example:

proc sql outobs=12;
   title1 'Low Temperatures in Celsius';
   title2 'First 12 Rows Only';
   select City, (AvgLow - 32) * 5/9 as LowCelsius format=4.1
      from worldtemps;
quit;

Assigning a Column Alias to a Calculated Column

Referring to a Calculated Column by Alias

When you use a column alias to refer to a calculated value, you must use the CALCULATED keyword with the alias to inform PROC SQL that the value is calculated within the query. The following example uses two calculated values, LowC and HighC, to calculate a third value, Range:

proc sql outobs=12;
   title1 'Range of High and Low Temperatures in Celsius';
   title2 'First 12 Rows Only';
   select City, (AvgHigh - 32) * 5/9 as HighC format=5.1, 
                (AvgLow - 32) * 5/9 as LowC format=5.1,
                (calculated HighC - calculated LowC)
                 as Range format=4.1
   from worldtemps;
quit;

Referring to a Calculated Column by Alias

For more information, see Using Column Aliases.

Assigning Values Conditionally

Using a Simple CASE Expression

CASE expressions enable you to interpret and change some or all of the data values in a column to make the data more useful or meaningful.

You can use conditional logic within a query by using a CASE expression to conditionally assign a value. You can use a CASE expression anywhere that you can use a column name.

The following table, which is used in the next example, describes the world climate zones (rounded to the nearest degree) that exist between Location 1 and Location 2:

World Climate Zones

Climate zone	Location 1	Latitude at Location 1	Location 2	Latitude at Location 2
North Frigid	North Pole	90	Arctic Circle	67
North Temperate	Arctic Circle	67	Tropic of Cancer	23
Torrid	Tropic of Cancer	23	Tropic of Capricorn	-23
South Temperate	Tropic of Capricorn	-23	Antarctic Circle	-67
South Frigid	Antarctic Circle	-67	South Pole	-90

In this example, a CASE expression determines the climate zone for each city based on the value in the Latitude column in the WorldCityCoords table. The query also assigns an alias of ClimateZone to the value. You must close the CASE logic with the END keyword.

proc sql outobs=12; title1 'Climate Zones of World Cities'; title2 'First 12 Rows Only'; select City, Country, Latitude, case when Latitude gt 67 then 'North Frigid' when 67 ge Latitude ge 23 then 'North Temperate' when 23 gt Latitude gt -23 then 'Torrid' when -23 ge Latitude ge -67 then 'South Temperate' else 'South Frigid' end as ClimateZone from worldcitycoords order by City; quit;

proc sql outobs=12;
   title1 'Climate Zones of World Cities';
   title2 'First 12 Rows Only';
   select City, Country, Latitude,
          case
             when Latitude gt 67 then 'North Frigid'
             when 67 ge Latitude ge 23 then 'North Temperate'
             when 23 gt Latitude gt -23 then 'Torrid'
             when -23 ge Latitude ge -67 then 'South Temperate'
             else 'South Frigid'
          end as ClimateZone
      from worldcitycoords
      order by City;
quit;

Using a Simple CASE Expression

Using the CASE-OPERAND Form

You can also construct a CASE expression by using the CASE-OPERAND form, as in the following example. This example selects states and assigns them to a region based on the value of the Continent column:

proc sql outobs=12; title1 'Assigning Regions to Continents'; title2 'First 12 Rows Only'; select Name, Continent, case Continent when 'North America' then 'Continental U.S.' when 'Oceania' then 'Pacific Islands' else 'None' end as Region from unitedstates; quit;

proc sql outobs=12;
   title1 'Assigning Regions to Continents';
   title2 'First 12 Rows Only';
   select Name, Continent,
          case Continent
             when 'North America' then 'Continental U.S.'
             when 'Oceania' then 'Pacific Islands' 
             else 'None'
          end as Region
      from unitedstates;
quit;

Note: When you use the CASE-OPERAND form of the CASE expression, the conditions must all be equality tests. That is, they cannot use comparison operators or other types of operators, as are used in Using a Simple CASE Expression.

Using a CASE Expression in the CASE-OPERAND Form

Replacing Missing Values

The COALESCE function enables you to replace missing values in a column with a new value that you specify. For every row that the query processes, the COALESCE function checks each of its arguments until it finds a nonmissing value, and then returns that value. If all of the arguments are missing values, then the COALESCE function returns a missing value. For example, the following query replaces missing values in the LowPoint column in the Continents table with the words Not Available:

proc sql;
   title 'Continental Low Points';
   select Name, coalesce(LowPoint, 'Not Available') as LowPoint
      from continents;
quit;

Using the COALESCE Function to Replace Missing Values

The following CASE expression shows another way to perform the same replacement of missing values. However, the COALESCE function requires fewer lines of code to obtain the same results:

proc sql;
   title 'Continental Low Points';
   select Name, case
                   when LowPoint is missing then 'Not Available'
                   else Lowpoint
                end as LowPoint
      from continents;
quit;

Specifying Column Attributes

You can specify the following column attributes, which determine how SAS data is displayed:

• FORMAT=
• INFORMAT=
• LABEL=
• LENGTH=

If you do not specify these attributes, then PROC SQL uses attributes that are already saved in the table or, if no attributes are saved, then it uses the default attributes.

The following example assigns a label of State to the Name column and a format of COMMA10. to the Area column:

proc sql outobs=12;
   title1 'Areas of U.S. States in Square Miles';
   title2 'First 12 Rows Only';
   select Name label='State', Area format=comma10. 
      from unitedstates;
quit;

select Name label='State', Area format=comma10.

select Name 'State', Area format=comma10.

Specifying Column Attributes