MONTH function not working properly in sashelp.prdsal3

[kishangabani]

proc sql;
 create table m as
 select distinct month 
 from sashelp.prdsal3
;
 quit;

MONTH

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Total month is 24.

proc sql;
select month,
			case
			when year=1997 then sum(actual)
			end as _1997
			,
			case
			when year=1998 then sum(actual)
			end as _1998
from sashelp.prdsal3

group by 1;

quit;

OUTPUT:

I want to properly to set month. I want to sum of column 'ACTUAL'.

[novinosrin]

Can you clarify your requirement again please without your code in simple sentences, like what you want to accomplish

 Do you want to sum the actual obs for year 1997 and 1998 ? 

[kishangabani]

Sorry, I want output like this

MONTH  1997  1998  column name

I don't want missing or null value. and, also want PROC SQL query if it's possible.

Thank You.

[Reeza · Accepted solution]

It's possible, but inefficient.

*Summary statistics;
proc means data=sashelp.prdsal3 noprint nway;
    class month year;
    var actual;
    output out=summary sum(actual)=total_sales;
run;

*format for display;
proc transpose data=summary out=want (drop=_:) prefix=Year;
    by month;
    id year;
    var total_sales;
run;

The SQL solution works but you have to know the years ahead of time AND code it for each year individually.

proc sql;
create table want_sql as
select put(month, monname3.) as month, 
sum(case when year=1997 then actual else 0 end) as year1997 format=dollar32.2,
sum(case when year=1998 then actual else 0 end) as year1998 format=dollar32.2
from sashelp.prdsal3
group by calculated month;
quit;

---

@kishangabani wrote:

Sorry, I want output like this

MONTH  1997  1998  column name

I don't want missing or null value. and, also want PROC SQL query if it's possible.

Thank You.

---

[Reeza]

PROC SQL doesn't respect SAS formats. So if your variable is a SAS date with a monname format you'll get this type of result. Instead you need to convert it over to an actual month value first, though this can cause other issues later on. 

This can replicate your problem:

    title;footnote;
    data demo;
    set sashelp.stocks;
    format date monname3.;
    run;

    proc sql;
    create table demo_wrong as
    select distinct date
    from demo;

    create table demo_right as
    select distinct put(date, monname3.) as monName
    from demo;
    quit;

    proc print data=demo_wrong (obs=20);
    proc print data=demo_right (obs=20);
    run;

---

@kishangabani wrote:

proc sql;
 create table m as
 select distinct month 
 from sashelp.prdsal3
;
 quit;

MONTH

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec

Total month is 24.

proc sql;
select month,
			case
			when year=1997 then sum(actual)
			end as _1997
			,
			case
			when year=1998 then sum(actual)
			end as _1998
from sashelp.prdsal3

group by 1;

quit;

OUTPUT:

I want to properly to set month. I want to sum of column 'ACTUAL'.

---

[Reeza]

Also, you'd be better off using PROC REPORT, TABULATE or MEANS to generate your results and then pivot it with PROC TRANSPOSE. Otherwise you must know all the years ahead of time, whereas the approach above would be dynamic instead.

[Tom]

One of the problems with using a format that maps multiple distinct values to the same displayed value.

The variable MONTH in that dataset has date values to which the MONNAME format has been attached. If you look at the actual values (or use a different format) you will see what is happening.  

proc sql  ;
select
  year
, month
, month(month) format=z2. as MonthNum 
, month as Day label='Day' format=yymmdd10.
, sum(actual) as Total
 from sashelp.prdsal3
 group by 1,2,3,4
 ;
 quit;

             Month
Year  Month    Num         Day     Total
----------------------------------------
1997    Jan     01  1993-01-01    407515
1997    Feb     02  1993-02-01    419927
1997    Mar     03  1993-03-01    396236
1997    Apr     04  1993-04-01    413626
1997    May     05  1993-05-01    417042
1997    Jun     06  1993-06-01    409394
1997    Jul     07  1993-07-01    423352
1997    Aug     08  1993-08-01    412309
1997    Sep     09  1993-09-01    421428
1997    Oct     10  1993-10-01    427881
1997    Nov     11  1993-11-01    415825
1997    Dec     12  1993-12-01    415795
1998    Jan     01  1994-01-01  444612.3
1998    Feb     02  1994-02-01  456635.3
1998    Mar     03  1994-03-01  465384.7
1998    Apr     04  1994-04-01  461707.4
1998    May     05  1994-05-01  455460.5
1998    Jun     06  1994-06-01    456412
1998    Jul     07  1994-07-01  471626.1
1998    Aug     08  1994-08-01  463702.8
1998    Sep     09  1994-09-01    450285
1998    Oct     10  1994-10-01  464220.9
1998    Nov     11  1994-11-01  464501.4
1998    Dec     12  1994-12-01  481884.7

[kishangabani]

yes, thank you.

but, I want to like

MONTH  1997  1998  <-- colmn name,

in month column only Jan-Dec for SUM(ACTUAL) of year.

[ballardw]

ONE: You never used the "month" function.

Two: Reports can often be done with a report procedure:

Proc tabulate data=sashelp.prdsal3;
   class month;
   class year;
   var actual;
   table month='',
         year=''*actual=''*sum=''
         /box='Month'
   ;
run;