topic Re: Group by on a created variable? in SAS Programming

Group by on a created variable?

G_I_Jeff — Wed, 02 Jun 2021 16:11:40 GMT

Trying the following code and it is not producing the results I expect. I am assuming it's because I'm using a defined variable in the sql in my group by statement?

data user.step_one;                                    
 length acct $10 strtdate $ 9 enddate $ 9 dsname $64;  
 input acct $ strtdate $ enddate $ size dsname $;      
 cards;                                                
  7733K772U1 01JUN2021 01JUN2021 100 KJDY01.TEST.ONE   
  7733K772U1 01JUN2021 01JUN2021 100 KJDY01.TEST.TWO   
  7733K772U1 01JUN2021 01JUN2021 100 KKSO01.TEST.ONE   
  7733K772U1 01JUN2021 01JUN2021 100 KJDY01.TEST.THREE 
  7733K772U1 01JUN2021 01JUN2021 100 KRRB01.TEST.ONE   
  7733K772U1 01JUN2021 01JUN2021 100 KRRB01.TEST.TWO   
  7733K772U1 01JUN2021 01JUN2021 100 KKSO01.TEST.TWO   
  7733K772U1 01JUN2021 01JUN2021 100 KRDO01.TEST.ONE   
run;                                                   
                                                       
proc sql;                                              
  select acct, strtdate, enddate, sum(size),           
         scan(dsname,1,'.') as hlq label='hlq'         
    from user.step_one                                 
    group by hlq, acct;

I expect just four lines of output with the summed size variable. I will try and run my summarization in another step and build the table with this code. Just curious.

Re: Group by on a created variable?

Kurt_Bremser — Wed, 02 Jun 2021 16:15:50 GMT

You need to include strtdate and enddate in the GROUP BY clause, otherwise you get the automatic remerge (which is also NOTEd in the log - Maxim 2!)

Re: Group by on a created variable?

G_I_Jeff — Wed, 02 Jun 2021 17:31:44 GMT

Thank you sir. That does what I expect. I changed one of my acct numbers and further tested the group by and we are off and running. I'm not sure I quite understand that logic but looks like I have some reading to do. Thank you for referencing the efficient programmer link.

Re: Group by on a created variable?

Kurt_Bremser — Wed, 02 Jun 2021 19:15:32 GMT

When youbdo GROUP BY in SQL, any item in the SELECT must either be

a constant
part of a summary function
or in the GROUP BY

If a variable that is not part of GROUP BY appears in the SELECT, other SQL flavors will throw an error, but SAS will do an automatic remerge which results in all observations being kept, and put a corresponding NOTE in the log.