topic Re: Proc SQL SAS : working with multiple queries in SAS Programming

Proc SQL SAS : working with multiple queries

sayanapex06 — Mon, 19 Mar 2018 10:16:35 GMT

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

Re: Proc SQL SAS : working with multiple queries

LinusH — Mon, 19 Mar 2018 11:07:14 GMT

Look like you are missing a FROM clause...

Re: Proc SQL SAS : working with multiple queries

gamotte — Mon, 19 Mar 2018 12:14:30 GMT

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;

Re: Proc SQL SAS : working with multiple queries

Kurt_Bremser — Mon, 19 Mar 2018 14:12:06 GMT

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.

Re: Proc SQL SAS : working with multiple queries

Kurt_Bremser — Mon, 19 Mar 2018 14:14:10 GMT

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-data-AKA-generate/ta-p/258712 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.