0

I want to sum up all values for the same cost centre per each year in SAS. I tried with Group By function in Proc SQL. 

PROC SQL

    CREATE TABLE incident_data AS

        SELECT   Cost_Centre_Dim.Cost_Centre_Name,
                 Sum(Count*Amount) AS TOT,
                 OCC_DATE format= Year4. 
        FROM     Incidents,
                 Incident_Type,
                 Cost_Centre_Dim 
        WHERE    Incidents.Incident_Code = Incident_Type.Incident_Code
        AND      Incidents.Cost_Centre_ID = Cost_Centre_Dim.Cost_Centre_ID
        GROUP BY Cost_Centre_Dim.Cost_Centre_Name,
                 OCC_DATE 
        ORDER BY [Cost Centre Dim].[Cost Centre Name];

QUIT;
RUN;

The output looks like below:

2012  34.41  ACCI2 
2012  34.23  ACCI2 
2014  25.71  INFR1 
2014  27.82  INFR1 
2014  22.26  INFR2 
2014  20.97  INFR2 
2013  22.64  ACCI4 
2013  19.29  ACCI4 
2013  18.26  ACCI4 
2014  35.82  ACCI4 
2015  97.81  INFR3 
2015  44.04  INFR3 
2014  57.09  INFR3

I want my output to show 1 line for 1 type of incident per year with the add up amount. Sth like this:

2012  68.64  ACCI2  
2014  53.53  INFR1  
2014  43.23  INFR2  
...

Any advice is really appreciated

Bex Haina
  • 3
  • 1
  • 4
  • Could you format the question a bit to make it more readable (prefix each line of code with 4 spaces). – kebs May 29 '15 at 14:02
  • I initially tried this in SQL Microsoft Access and it worked well but not in SAS. Does SAS have trouble grouping multiple columns when one of columns is numeric? – Bex Haina May 29 '15 at 14:10
  • Is `Year.4` a typo? IT should be `year4.`. Other than that, I don't see any problems. Are there notes in the log? – DWal May 29 '15 at 14:56
  • yes it was a typo when I transferred my code to here. Oliver helped me solve the problem by using put (date, yearw.) – Bex Haina May 30 '15 at 11:32

1 Answers1

0

Try converting OCC_DATE to text like this: put(OCC_DATE, year4) as MyOCC_DATE

I think the original code is only displaying the date in the format you assigned but it is still the actual numerical value of OCC_DATE. If you change it to character in the format you want then it should do the grouping correctly.

Oliver
  • 194
  • 4
  • 10