Proc Sql Unique

Name: Proc Sql Unique
Published: neyceaportproj1986

There are several ways to use SAS to get the unique values for a data variable. In Base SAS. you can use the TABLES statement in PROC FREQ to generate a table of unique values (and the counts). You can also use the DISTINCT function in PROC SQL to get the same information. In the SAS/IML language. you can use the UNIQUE function to find the unique values of a vector. In all these cases. the values are returned in sorted (alphanumeric) order. For example. here are three ways to obtain the unique values of the TYPE variable in the Sashelp. Cars data set. Only the result from PROC IML is shown:

Recently a colleague asked how to obtain the unique values of a SAS/IML vector in the order in which they appear in the vector. I thought it might also be useful to support an option to return the unique values in (descending) order of frequency. so I wrote the following function. The first argument to the UniqueOrder function is the data vector. The second (optional) argument is a string that determines the order of the unique values. If the second argument has the value "DATA". then the unique values are returned in data order. If the second argument has the value "FREQ". then the unique values are returned in descending order by frequency. The function is shown below. I test the function on a character vector (TYPE) and a numeric vector (CYLINDERS) that contains some missing values.

In summary. whether you are implementing an analysis in Base SAS or SAS/IML. this article shows how to obtain the unique values of data in sorted order. in order of frequency. or in the order that the values appear in the data.

Depuis SAS 9. 2. un nouveau WARNING apparaît dans la log quand le nom de la table SAS d’entrée est le même que le nom de la table SAS de sortie. Pour éviter ce message. l’option undo_policy=none est à ajouter dans l’instruction proc sql;. SAS support prévient néanmoins que l’usage du même nom en entrée et en sortie présente un risque et que l’option enlève l’avertissement mais pas le risque.

Avec PROC SQL. je crée une nouvelle table du même nom que la table source. Dans un data set je réduis la longueur d’une variable 1. Utiliser le même nom de data set en entré et en sortie avec PROC SQL

Le message dans la log ressemble à ceci: WARNING: This CREATE TABLE statement recursively reference the target table. A consequence of this is a possible data integrity problem.

Une solution pour changer la longueur d’une variable est de définir sa longueur dans une instruction LENGTH avant de lire les données avec un SET par exemple.

Depuis SAS 9. 2. si la nouvelle longueur est plus petite que l’ancienne un WARNING apparaît dans la log. Ceci est une bonne chose car cela vous permet de repérer d’éventuelles coupures (truncations) de vos données.

Dans certains cas cependant. vous savez pertinemment que le nombre de caractères dans données est au plus X et que dès lors elles ne seront pas coupées en réduisant la longueur.

Notez cependant. qu’avec SAS 9. 1. 3 ou SAS 9. 2. un warning apparaît dès lors que la variable est donnée dans une instruction BY. L’option VARLENCHK de SAS 9. 2 ne vous enlèvera pas le warning. Je vous conseille donc de changer votre longueur au préalable si vous ne voulez pas ce WARNING.

WARNING: Multiple lengths were specified for the BY variable x by input data sets and LENGTH. FORMAT. INFORMAT statements. This may cause unexpected results.

L’intérêt est de souvent de pouvoir ensuite empiler des data sets ayant des longueurs de variables. En effet la longueur d’une variable rencontrée dans le premier data set sera la longueur de référence. Il ne faut pas qu’elle soit plus petite que celle du data set suivant. Autrement le texte des observations d’après est coupé (truncated).

Proc sql; create table empty ( var_text char (20) label=‘Var. caractère. longueur 20’ . var_num_dt num label=‘Var. numérique. longueur 8’ format=date9. ) ; quit;

Because you are using an older version of Internet Explorer. the SAS document you are accessing cannot be displayed correctly. You have several options:

Optional Arguments UNIQUE causes SAS to reject any change to a table that would cause more than one row to have the same index value. Unique indexes guarantee that data in one column. or in a composite group of columns. remains unique for every row in a table. A unique index can be defined for a column that includes NULL or missing values if each row has a unique index value.

Comparisons against a column that is indexed an IN subquery where the column in the inner subquery is indexed correlated subqueries. where the column being compared with the correlated reference is indexed join-queries. where the join-expression is an equals comparison and all the columns in the join-expression are indexed in one of the tables being joined. SAS maintains indexes for all changes to the table. whether the changes originate from PROC SQL or from some other source. Therefore. if you alter a column's definition or update its values. then the same index continues to be defined for it. However. if an indexed column in a table is dropped. then the index on it is also dropped.

You can create simple or composite indexes. A simple index is created on one column in a table. A simple index must have the same name as that column. A composite index is one index name that is defined for two or more columns. The columns can be specified in any order. and they can have different data types. A composite index name cannot match the name of any column in the table. If you drop a composite index. then the index is dropped for all the columns named in that composite index.

See the section on SAS files in SAS Language Reference: Concepts for a further description of when to use indexes and how they affect SAS statements that handle BY-group processing.

CHECK (WHERE-clause) specifies that all rows in table-name satisfy the WHERE-clause. WHERE-clause is a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

PRIMARY KEY specifies that the column is a primary key column. that is. a column that does not contain missing values and whose values are unique.

CASCADE allows primary key data values to be updated. and updates matching values in the foreign key to the same values. This referential action is currently supported for updates only.

CONSTRAINT constraint-name constraint message-string' message-type>> constraint-name specifies a name for the constraint that is being specified. The name must be a valid SAS name.

Note: The names PRIMARY. FOREIGN. MESSAGE. UNIQUE. DISTINCT. CHECK. and NOT cannot be used as values for constraint-name. constraint is one of the following constraint types:

CHECK WHERE-clause specifies that all rows in table-name satisfy the WHERE-clause. WHERE-clause is a SAS WHERE clause. Do not include the WHERE keyword in the WHERE clause.

DISTINCT (column-1 column-2. . . . >) specifies that the values of each column must be unique. This constraint is identical to UNIQUE.

FOREIGN KEY (column-1 column-2. . . . >) REFERENCES table-name referential-action> referential-action> specifies a foreign key. that is. a set of columns whose values are linked to the values of the primary key variable in another table (the table-name that is specified for REFERENCES). The referential-actions are performed when the values of a primary key column that is referenced by the foreign key are updated or deleted. referential-actions can be the following action: