Aliases and Group By Statements in SAS Proc SQL

Question

I am working with proc SQL in SAS and one of my proc sql queries is behaving very oddly:

I have a large data set (about 1 Million rows), which looks something like this:

apple_key    profit    price    cost    months    date      
golden_d     0.03      12       4       3         01/12
golden_d     0.03      8        0       2         01/12
granny_s     0.05      15       5       5         02/12
red_d        0.04      13       0       1         01/12
golden_d     0.02      1        2       12         03/14

On this data set I am running the following query:

%let picking_date = 01/12; /* I simplify here - this part of my code definitely works */

proc sql; 
    CREATE TABLE output AS 
    SELECT 
        (CASE apple_key
              WHEN "golden_d" THEN 1
              WHEN "granny_s" THEN 2
              WHEN "red_d"    THEN 3
        END) AS apple_id,
        apple_key AS apple_name,
        (CASE WHEN cost= 0 THEN 0 
            ELSE 1 
        END) AS cost_flag,
        (CASE 
            WHEN CEIL(months / 2) < 5 THEN CEIL(months / 2) 
            ELSE 5
        END) AS age, 
        "McDonalds" as farm, 
        sum(profit*price)/sum(price) as price_weighted_profit
    FROM input_table
    WHERE date = "&picking_date."d
        AND price > cost
        AND cost >= 0
        AND cost >= 0
    GROUP BY apple_id, apple_name, cost_flag, age, farm
    ; 
run;

When I run this my GROUP BYstatement does not work. I get a bunch of entries for a single group (where apple_id, apple_name, cost_flag, age and farm are all the same, but my aggregation is not working).

However, when I run the GROUP BY separately (as follows) everything works perfectly fine. I get one entry for each group with a "price weighted profit":

proc sql; 
    CREATE TABLE output_tmp AS 
    SELECT 
        (CASE apple_key
              WHEN "golden_d" THEN 1
              WHEN "granny_s" THEN 2
              WHEN "red_d"    THEN 3
        END) AS apple_id,
        apple_key AS apple_name,
        (CASE WHEN cost= 0 THEN 0 
            ELSE 1 
        END) AS cost_flag,
        (CASE 
            WHEN CEIL(months / 2) < 5 THEN CEIL(months / 2) 
            ELSE 5
        END) AS age, 
        "McDonalds" as farm
    FROM input_table
    WHERE date = "&picking_date."d
        AND price > cost
        AND cost >= 0
        AND cost >= 0
   ;

    CREATE TABLE output AS
    SELECT 
        apple_id, 
        apple_name, 
        cost_flag, 
        age, 
        farm,
        sum(profit*price)/sum(price) as price_weighted_profit
    FROM output_tmp
    GROUP BY apple_id, apple_name, cost_flag, age, farm
    ;
quit;

Why is this happening? How can I fix it? This is driving me a little bit crazy... Thanks up front for the help

Are you trying to calculate the count or sum by doing a group by? — Teja, Apr 24 '17 at 20:15
Sorry. Had forgotten to move the summary function statement to the second `create table´ statement. Should be correct now. I am trying to calculate "price_weighted_profit" here — Martin Reindl, Apr 24 '17 at 20:26
What output do you expect from your sample input? What output are you getting? — Tom, Apr 24 '17 at 22:47

India.Rocket · Accepted Answer · 2017-04-24T20:21:11.293

It doesn't work because group by is not taking sum(profit*price)/sum(price) statement as aggregated function. it does not do that because of aliases like age, cost_flag etc.

Anyways below is the right query:-

 Proc sql;
    CREATE TABLE output AS 
     SELECT 
            apple_id, 
            apple_name, 
            cost_flag, 
            age, 
            farm, 
            sum(profit*price)/sum(price) as price_weighted_profit
        FROM
       (
        SELECT 
            (CASE apple_key
                  WHEN "golden_d" THEN 1
                  WHEN "granny_s" THEN 2
                  WHEN "red_d"    THEN 3
            END) AS apple_id,
            apple_key AS apple_name,
            (CASE WHEN cost= 0 THEN 0 
                ELSE 1 
            END) AS cost_flag,
            (CASE 
                WHEN CEIL(months / 2) < 5 THEN CEIL(months / 2) 
                ELSE 5
            END) AS age, 
            "McDonalds" as farm
        FROM input_table
        WHERE date = "&picking_date."d
            AND price > cost
            AND cost >= 0
            AND cost >= 0

        ) a
        GROUP BY apple_id, apple_name, cost_flag, age, farm;
        quit;

Let me know if you have any questions

Thanks so much. This one solved everything. Just to make sure I understand: an `alias` is what I get every time I use an `AS` statement These aliases are preventing the `GROUP BY` statement from grouping correctly. Could you help me understand why this is happening? As in: why doesn't it recognize my aliases? — Martin Reindl, Apr 24 '17 at 20:43
yup. your understanding is correct. That's a bug or I don't know that's how SAS works. Gotta live with it — India.Rocket, Apr 24 '17 at 20:44

score 0 · Answer 2 · answered Apr 24 '17 at 20:17

Thumb rule :- Whenever you use any aggregate functions in your select clause the remaining columns should be part of the group by. In your question you posted you are apply sum(profit*price)/sum(price) but there is no group by which is causing the problem.

Proc sql;
    CREATE TABLE output AS 
        SELECT 
            (CASE apple_key
                  WHEN "golden_d" THEN 1
                  WHEN "granny_s" THEN 2
                  WHEN "red_d"    THEN 3
            END) AS apple_id,
            apple_key AS apple_name,
            (CASE WHEN cost= 0 THEN 0 
                ELSE 1 
            END) AS cost_flag,
            (CASE 
                WHEN CEIL(months / 2) < 5 THEN CEIL(months / 2) 
                ELSE 5
            END) AS age, 
            "McDonalds" as farm, 
            sum(profit*price)/sum(price) as price_weighted_profit
        FROM input_table
        WHERE date = "&picking_date."d
            AND price > cost
            AND cost >= 0
            AND cost >= 0    
        GROUP BY apple_id, apple_name, cost_flag, age, farm;
        quit;

Hi. Thanks for the help, but as mentioned above I am using a `GROUP BY` in both of the versions above. One of them just doesnt seem to be working just isn`t working. Or maybe I'm misunderstanding you? Could you be more specific? — Martin Reindl, Apr 24 '17 at 20:31
Are you able to create table with the above sql I have written? — Teja, Apr 24 '17 at 20:32

score 0 · Answer 3 · answered Apr 24 '17 at 20:18

I suspect that what is happening is remerging. SAS proc sql accepts code like this:

proc sql;
    select a.*, count(*)
    from a;

This does not summarize the data. Instead it puts the overall count on each row. In other words, if the keys in the select don't exactly match the group by, then the aggregation functions are calculated based on the group by keys, but the results are put onto the individual rows. Other databases do this with a subset of the window functions.

In your case, remerging is not obvious. I think there is key confusion because you are using the same names in the select as in the original data. My advice is to change the aliases so they are unambiguous and be sure that the keys in the group by are unambiguous.

Aliases and Group By Statements in SAS Proc SQL

3 Answers3