Inheriting Column Attributes (SQL)

From sasCommunity
Jump to: navigation, search

When a column is created in PROC SQL, using a column name found in an input table does not assure that column attributes (label, format, etc.) will be inherited. For example, consider the table created using this code:

 data before;
 input a b;
 format a b 4.1;
 datalines;
 1.11  1.22
 2.11  2.22
 ;

Use a DATA step to transform one of the columns.

 data after;
 set before;
 a= a + 0.001;
 run;

Column A in the new table (AFTER) still has the 4.1 format it had in BEFORE. Confirm that by running

 proc sql;
 select * from after;

The result is

   a     b
----------
 1.1   1.2
 2.1   2.2

Now do the same thing using SQL for the transformation:

 select a + 0.001 as a, b from before;

The result is

       a     b
--------------
   1.111   1.2
   2.111   2.2

Column B inherits a format from the input table, because it is SELECTed simply by its name. In contrast, the new column A is separate from the column A in the source table (BEFORE) and therefore has the default format for a numeric column (BEST8.).

Here is a trick which can be used to make the output inherit such attributes. Use an OUTER UNION to introduce a zero-row template. In this example, the code would be

 select a             , b from before(obs=0)
 outer union corresponding
 select a + 0.001 as a, b from before;

The result is

   a     b
----------
 1.1   1.2
 2.1   2.2

Such a template can be used to control column names and column order in addition to column attributes.


The "inheritance" of union'ed attributes can be a bit more complicated, though. Consider the case of adjusting the length of a character variable when appending.

We first create tables one and two with the same variables but with different lengths:

  create table one as
  select "abc" as x length=3, "ab" as y length=2
  from   sashelp.class(obs=1);
 
  create table two as
  select "12" as x length=2, "12345" as y length=5
  from   sashelp.class(obs=1);

Then append the table two to one using union. (Optionally, after the keyword, union, you can add all if you want to keep all the duplicated records; add corr if you want to match columns by name, not by the column order):

  create table three as
  select * from one
  union   
  select * from two;

The main point of this exercise is that the length of the variables are automatically adjusted to the longest, as shown below.

  select name, length
  from   dictionary.columns
  where  libname="WORK" and memname="THREE";
 /* on lst
                Column
 Column Name    Length
 ---------------------
 x                   3
 y                   5
 */
 
 quit;