topic Re: Select clause - calculating new column in SAS Programming

Select clause - calculating new column

Leon_Seungmin — Mon, 24 Oct 2016 12:07:35 GMT

Hello all,

I have one simple question about select clause. I wanted to calculate a new column based on columns that was created within the same select clause. Then I have encountered the error message saying "The following columns were not found in the contributing tables: TOT_CON_NUM, TOT_STR_NUM."

This is the log.

-------------------------------

93 proc sql;
94 create table KLD_t as
95 select
96 CUSIP,
97 YEAR,
98 COMPANYNAME,
99 COMPANYID,
100 TICKER,
101 COM_STR_NUM,
102 COM_CON_NUM,
103 DIV_STR_NUM,
104 DIV_CON_NUM,
105 EMP_STR_NUM,
106 EMP_CON_NUM,
107 ENV_STR_NUM,
108 ENV_CON_NUM,
109 PRO_STR_NUM,
110 PRO_CON_NUM,
111 COM_STR_NUM + DIV_STR_NUM + EMP_STR_NUM + ENV_STR_NUM + PRO_STR_NUM AS TOT_STR_NUM,
112 COM_CON_NUM + DIV_CON_NUM + EMP_CON_NUM + ENV_CON_NUM + PRO_CON_NUM AS TOT_CON_NUM,
113 TOT_STR_NUM - TOT_CON_NUM as KLD_SCORE
114 from kld.kld
115 quit;
ERROR: The following columns were not found in the contributing tables: TOT_CON_NUM,
TOT_STR_NUM.

---------------------------------------

I understand that I can still calculate KLD_SCORE after making a table with TOT_STR_NUM and TOT_CON_NUM then calculating KLD_SCORE again, but I am sure there's a way to do it just at once.

Thank you for your help all the time!

Re: Select clause - calculating new column

Oligolas — Mon, 24 Oct 2016 12:19:03 GMT

Hi Leon,

use the keyword "calculated".

like this:

PROC SQL;
   CREATE TABLE KLD_t AS
      SELECT CUSIP,
             YEAR,
             COMPANYNAME,
             COMPANYID,
             TICKER,
             COM_STR_NUM,
             COM_CON_NUM,
             DIV_STR_NUM,
             DIV_CON_NUM,
             EMP_STR_NUM,
             EMP_CON_NUM,
             ENV_STR_NUM,
             ENV_CON_NUM,
             PRO_STR_NUM,
             PRO_CON_NUM,
             COM_STR_NUM + DIV_STR_NUM + EMP_STR_NUM + ENV_STR_NUM + PRO_STR_NUM AS TOT_STR_NUM,
             COM_CON_NUM + DIV_CON_NUM + EMP_CON_NUM + ENV_CON_NUM + PRO_CON_NUM AS TOT_CON_NUM,
             calculated TOT_STR_NUM - calculated TOT_CON_NUM AS KLD_SCORE
      FROM kld.kld
   ;
QUIT;

Re: Select clause - calculating new column

Leon_Seungmin — Mon, 24 Oct 2016 12:21:42 GMT

Thank you very much!