BookmarkSubscribeRSS Feed
sayanapex06
Obsidian | Level 7

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

4 REPLIES 4
LinusH
Tourmaline | Level 20

Look like you are missing a FROM clause...

Data never sleeps
gamotte
Rhodochrosite | Level 12

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
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1031 views
  • 0 likes
  • 4 in conversation