DATA Step, Macro, Functions and more

SQL DATE GROUP BY help

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 75
Accepted Solution

SQL DATE GROUP BY help

I have the following code:

PROC SQL;                                   
 SELECT SYSTEM,                             
        PUT(DATE,YYMMD7.) AS DATE,          
        ACCOUNT1,                           
        TYPETASK,                           
        SUM(CPUTOTTM) AS CPUSECS,           
        SUM(CPUZIPTM) AS ZIPSECS            
   FROM USER.TMPSMF                         
  WHERE TYPETASK IN ('JOB','TSU') AND       
        ACCOUNT1 IN ('HHS000V8',            
                     'PG83BAKT',            
                     'PVVABBKZ',            
                     'RF2JS4AR',            
                     'RGGS7PAA',            
                     'RGXP44AY',            
                     'THAFRCKC',            
                     'TJDBA6GA',            
                     'TJNCJLGF',            
                     'RM9ZYWLG')            
  GROUP BY SYSTEM, DATE, ACCOUNT1, TYPETASK;
QUIT;                                       
RUN;                                        

I am trying to group by date in MMYYYY but it's not working and I don't understand why. I've tried several different formats. Do I need to set the format before I perform this SQL call?

 


Accepted Solutions
Solution
‎02-02-2017 05:35 PM
Respected Advisor
Posts: 4,919

Re: SQL DATE GROUP BY help

You must tell SAS which version of DATE you are talking about in your group by clause. Try

 

PROC SQL;                                   
 SELECT SYSTEM,                             
        PUT(DATE,YYMMD7.) AS DATE,          
        ACCOUNT1,                           
        TYPETASK,                           
        SUM(CPUTOTTM) AS CPUSECS,           
        SUM(CPUZIPTM) AS ZIPSECS            
   FROM USER.TMPSMF                         
  WHERE TYPETASK IN ('JOB','TSU') AND       
        ACCOUNT1 IN ('HHS000V8',            
                     'PG83BAKT',            
                     'PVVABBKZ',            
                     'RF2JS4AR',            
                     'RGGS7PAA',            
                     'RGXP44AY',            
                     'THAFRCKC',            
                     'TJDBA6GA',            
                     'TJNCJLGF',            
                     'RM9ZYWLG')            
  GROUP BY SYSTEM, calculated DATE, ACCOUNT1, TYPETASK;
QUIT;     
PG

View solution in original post


All Replies
Super User
Posts: 19,770

Re: SQL DATE GROUP BY help


G_I_Jeff wrote:

but it's not working and I don't understand why.

 


What does not working mean?

Frequent Contributor
Posts: 75

Re: SQL DATE GROUP BY help

[ Edited ]

Sorry Reeza,

 

You're right. I forgot to pose my underlying question.

 

It's not sorting the date as MMYYYY. Its still sorting as YYYYMMDD.

 

Jeff

Super User
Posts: 11,343

Re: SQL DATE GROUP BY help

Describe not working, no result, unexpected result, error?

 

Also, is your date actuall a SAS date value and not perhaps a DATETIME value? If your value is date time value then you would see results like ******* as you are trying to put the number seconds into a layout expecting numbers of days.

Frequent Contributor
Posts: 75

Re: SQL DATE GROUP BY help

It is a DATETIME observation I'm pulling DATEPART from.

 

I'm trying to group the data by Month/Year based off the only date.

Frequent Contributor
Posts: 83

Re: SQL DATE GROUP BY help

[ Edited ]

My PROC SQL is a bit rusty since I've been primarily working with explicit pass through SQL to Oracle, but try something like this.

 

Looks like PG Stats suggested basically the same thing.

 

PROC SQL;                                   
 SELECT SYSTEM,                             
        PUT(DATE, mmyy7.) AS DATE,       
        ACCOUNT1,                           
        TYPETASK,                           
        SUM(CPUTOTTM) AS CPUSECS,           
        SUM(CPUZIPTM) AS ZIPSECS            
   FROM USER.TMPSMF                         
  WHERE TYPETASK IN ('JOB','TSU') AND       
        ACCOUNT1 IN ('HHS000V8',            
                     'PG83BAKT',            
                     'PVVABBKZ',            
                     'RF2JS4AR',            
                     'RGGS7PAA',            
                     'RGXP44AY',            
                     'THAFRCKC',            
                     'TJDBA6GA',            
                     'TJNCJLGF',            
                     'RM9ZYWLG')            
  GROUP BY SYSTEM, PUT(DATE, mmyy7.), ACCOUNT1, TYPETASK;
QUIT;        

 

 

Solution
‎02-02-2017 05:35 PM
Respected Advisor
Posts: 4,919

Re: SQL DATE GROUP BY help

You must tell SAS which version of DATE you are talking about in your group by clause. Try

 

PROC SQL;                                   
 SELECT SYSTEM,                             
        PUT(DATE,YYMMD7.) AS DATE,          
        ACCOUNT1,                           
        TYPETASK,                           
        SUM(CPUTOTTM) AS CPUSECS,           
        SUM(CPUZIPTM) AS ZIPSECS            
   FROM USER.TMPSMF                         
  WHERE TYPETASK IN ('JOB','TSU') AND       
        ACCOUNT1 IN ('HHS000V8',            
                     'PG83BAKT',            
                     'PVVABBKZ',            
                     'RF2JS4AR',            
                     'RGGS7PAA',            
                     'RGXP44AY',            
                     'THAFRCKC',            
                     'TJDBA6GA',            
                     'TJNCJLGF',            
                     'RM9ZYWLG')            
  GROUP BY SYSTEM, calculated DATE, ACCOUNT1, TYPETASK;
QUIT;     
PG
Frequent Contributor
Posts: 75

Re: SQL DATE GROUP BY help

Thank you! I didn't realize you could/had to put the calculated function or the format in the actual GROUP statement. Now I know!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 181 views
  • 0 likes
  • 5 in conversation