SAS SQL where clause on alias failing

Question

I am trying to subset the data using a where clause as below:

proc sql outobs=100;
create table Dbtr_Clnt_Generl_Inf as
  select FACS_Schema_ID '',
        'DBACCT*'n as ACCOUNT_NUM '',
        input(DBLSTDTI,yymmdd10.) as Date_Listed format=date09.
  from sqlsrv10.Acct_Dbtr_Clnt_Generl_Inf
  where Date_Listed >= '01Sep2016'd
  ;
quit;

But I am getting an error: ERROR: The following columns were not found in the contributing tables: Date_Listed.

Tom Tom · Accepted Answer · 2016-09-13T20:04:41

In PROC SQL you need to use the CALCULATED keyword when referencing a derived variable.

where calculated Date_Listed >= '01Sep2016'd

Or just reference the original variable instead of the derived one

where DBLSTDTI='2016-09-01'

SAS SQL where clause on alias failing

1 Answers