topic Re: SQL DATE GROUP BY help in SAS Programming

SQL DATE GROUP BY help

G_I_Jeff — Thu, 02 Feb 2017 21:24:45 GMT

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?

Re: SQL DATE GROUP BY help

Reeza — Thu, 02 Feb 2017 21:31:43 GMT

@G_I_Jeff wrote:

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

What does not working mean?

Re: SQL DATE GROUP BY help

G_I_Jeff — Thu, 02 Feb 2017 21:39:26 GMT

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

Re: SQL DATE GROUP BY help

ballardw — Thu, 02 Feb 2017 21:43:15 GMT

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.

Re: SQL DATE GROUP BY help

G_I_Jeff — Thu, 02 Feb 2017 21:51:19 GMT

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.

Re: SQL DATE GROUP BY help

PGStats — Thu, 02 Feb 2017 22:08:43 GMT

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;

Re: SQL DATE GROUP BY help

Sven111 — Thu, 02 Feb 2017 22:14:23 GMT

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;

Re: SQL DATE GROUP BY help

G_I_Jeff — Thu, 02 Feb 2017 22:36:14 GMT

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!