Proc SQL SAS : working with multiple queries

[sayanapex06]

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

[LinusH]

Look like you are missing a FROM clause...

[gamotte]

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;

[Kurt_Bremser]

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.

[Kurt_Bremser]

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.