DATA Step, Macro, Functions and more

Select clause - calculating new column

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Select clause - calculating new column

[ Edited ]

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!


Accepted Solutions
Solution
‎10-24-2016 08:21 AM
Frequent Contributor
Posts: 129

Re: Select clause - calculating new column

[ Edited ]
Posted in reply to Leon_Seungmin

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;

 

________________________

- Cheers -

View solution in original post


All Replies
Solution
‎10-24-2016 08:21 AM
Frequent Contributor
Posts: 129

Re: Select clause - calculating new column

[ Edited ]
Posted in reply to Leon_Seungmin

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;

 

________________________

- Cheers -

Contributor
Posts: 22

Re: Select clause - calculating new column

Thank you very much!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 209 views
  • 0 likes
  • 2 in conversation