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!
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 -
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 -
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.