BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
G_I_Jeff
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
3 REPLIES 3
Kurt_Bremser
Super User

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!)

G_I_Jeff
Obsidian | Level 7

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.

Kurt_Bremser
Super User

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.

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
  • 3 replies
  • 559 views
  • 0 likes
  • 2 in conversation