Hi ,
I have a SAS Dataset names Cloudability.
I am performing this query on the dataset :
PROC SQL ;
CREATE TABLE CLOUD_FINAL_PREM_SUP_NONPDX AS
SELECT BUSINESS_UNIT,PAYERACCOUNTID,LINKEDACCOUNTID,ACCOUNTNAME,
( (SUM(VENDOR_COST_OF_SERVICE)/TOTAL_COST_NONPREDIX) *
(AWS_PREMIUM_SUPPORT - PREMIUM_SUPPORT_PREDIX) )
AS PREMIUM_SUPPORT FROM CLOUDABILITY
WHERE TOTAL_COST_NONPREDIX IN
( SELECT SUM(VENDOR_COST_OF_SERVICE) AS TOTAL_COST_NONPREDIX
FROM CLOUDABILITY WHERE BUSINESS_UNIT NOT IN ('PREDIX')
AND PRODUCTNAME NOT IN ('AWS PREMIUM SUPPORT') )
AND AWS_PREMIUM_SUPPORT IN
( SELECT SUM(VENDOR_COST_OF_SERVICE) AS AWS_PREMIUM_SUPPORT
FROM CLOUDABILITY WHERE PRODUCTNAME IN ('AWS PREMIUM SUPPORT') )
AND PREMIUM_SUPPORT_PREDIX IN
( SELECT SUM(VENDOR_COST_OF_SERVICE) AS PREMIUM_SUPPORT_PREDIX
FROM CLOUDABILITY WHERE BUSINESS_UNIT NOT IN ('PREDIX') )
AND BUSINESS_UNIT NOT IN ('PREDIX')
AND PRODUCTNAME NOT IN ('AWS PREMIUM SUPPORT')
GROUP BY BUSINESS_UNIT,PAYERACCOUNTID,LINKEDACCOUNTID,ACCOUNTNAME
;
QUIT ;
And I am getting an error :
ERROR: The following columns were not found in the contributing tables:AWS_PREMIUM_SUPPORT, PREMIUM_SUPPORT_PREDIX, TOTAL_COST_NONPREDIX
Please help me with this
Look like you are missing a FROM clause...
Hello, try to describe your problem with a simple example that can be replicated
by SAS forum users.
The following query on sashelp.class has a form similar to your example :
proc sql;
SELECT sum(HEIGHT1,WEIGHT)
FROM sashelp.class
WHERE HEIGHT1 IN (
SELECT sum(HEIGHT) AS HEIGHT1
FROM sashelp.class
WHERE HEIGHT>60
);
quit;
You cannot use the varaible name HEIGHT1 created in the subquery in the main query.
You can do as follows instead :
proc sql;
SELECT sum(sum((HEIGHT>60)*HEIGHT),WEIGHT)
FROM sashelp.class
;
quit;
After reformatting your code to make it readable:
proc sql;
create table CLOUD_FINAL_PREM_SUP_NONPDX as
select
BUSINESS_UNIT,
PAYERACCOUNTID,
LINKEDACCOUNTID,
ACCOUNTNAME,
(
sum(VENDOR_COST_OF_SERVICE) / TOTAL_COST_NONPREDIX *
(AWS_PREMIUM_SUPPORT - PREMIUM_SUPPORT_PREDIX)
) as PREMIUM_SUPPORT
from CLOUDABILITY
where
TOTAL_COST_NONPREDIX in (
select sum(VENDOR_COST_OF_SERVICE) as TOTAL_COST_NONPREDIX
from CLOUDABILITY
where BUSINESS_UNIT ne 'PREDIX' and PRODUCTNAME ne 'AWS PREMIUM SUPPORT'
)
and
AWS_PREMIUM_SUPPORT in (
select sum(VENDOR_COST_OF_SERVICE) as AWS_PREMIUM_SUPPORT
from CLOUDABILITY
where PRODUCTNAME = 'AWS PREMIUM SUPPORT'
)
and
PREMIUM_SUPPORT_PREDIX in (
select sum(VENDOR_COST_OF_SERVICE) as PREMIUM_SUPPORT_PREDIX
from CLOUDABILITY
where BUSINESS_UNIT ne 'PREDIX'
)
and
BUSINESS_UNIT ne 'PREDIX'
and
PRODUCTNAME ne 'AWS PREMIUM SUPPORT'
group by BUSINESS_UNIT,PAYERACCOUNTID,LINKEDACCOUNTID,ACCOUNTNAME
;
quit;
I see no obvious problem with the SQL structure, all selects have their from.
Do a proc contents on dataset cloudability to see if all columns/variables are there.
Note: we have left the stone-age of computing with the introduction of the ASCII table and basically all compilers/interpreters can read lowercase perfectly well. No need to shout at them anymore.
PS It would be of great help if you used the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to create a datastep version of your dataset and post that here. Follow the advice in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce for posting code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.