topic Re: SQL - where a variable is in another in SAS Programming

SQL - where a variable is in another

SasStatistics — Thu, 10 Jun 2021 09:37:50 GMT

I have come across the following code (modified naming for ease of interpretation):

Proc sql;
   Delete from MyTable
   Where variable1 in (select variable1 from To_Delete);
Quit;

My interpretation of this code is the following:

We delete all observations (rows) where variable1 from the table MyTable (notice there is a variable1 in two different tables and hence the variable1 could differ from the two tables) takes the values of variable1 from the table ToDelete.

So, to make it easier to understand, I could write the pseudo-code:

Proc sql;
   Delete from MyTable
   Where variable1_MyTable in (select variable_1_ToDelete from To_Delete);
Quit;

Now it is clear that we delete all observations where variable1_MyTable takes the values from: variable_1_ToDelete.

If i understand it correctly, this seems like a powerful concept.

Questions:
1. Is my understanding of it correct?

2. Where can I read some documentation about this?

Thanks.

Re: SQL - where a variable is in another

Sajid01 — Thu, 10 Jun 2021 11:15:21 GMT

1. Yes your understanding is correct.
2.For more details you can look here

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/titlepage.htm

Re: SQL - where a variable is in another

Tom — Thu, 10 Jun 2021 12:14:32 GMT

To make it clearer which variable you are referencing you can use ALIAS prefix on the variable references.

Example:

3146  data male class ;
3147    set sashelp.class;
3148    output class;
3149    if sex='M' then output male;
3150  run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.MALE has 10 observations and 5 variables.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds


3151
3152  proc sql;
3153  delete from class a
3154    where a.name in (select b.name from male b)
3155  ;
NOTE: 10 rows were deleted from WORK.CLASS.

3156  quit;

Re: SQL - where a variable is in another

SasStatistics — Thu, 10 Jun 2021 14:04:38 GMT

Thank you Tom, very clear!

1. When aliasing, you are omitting AS? If I would like to be very clear, I would write:

proc sql;
delete from class as a
where a.name in (select b.name from male as b);
quit;

2. The method used here (Defining the values of a variable from another variable), it can in many situations produce the same result as an inner join for instance? Am I thinking about it correctly or what do you think is the main virtue of this method?

Re: SQL - where a variable is in another

Tom — Thu, 10 Jun 2021 14:22:32 GMT

SQL implementations differ on whether you can (or are required to) include AS keyword.

Personally I do not use it when defining an alias for a dataset (table) but do use it when setting the name of a variable (column). The AS keyword is required when creating a dataset (table) from the results of a query with the CREATE TABLE syntax.

proc sql;
create table want as 
  select a.name as Student_Name
  from sashelp.class a
;
quit;