TIme

[Boa]

Please delete this thread.

[RW9]

Sorry, your question really isn't clear.  What do you mean by "filter out", do you want to create datasets with these conditions?

data want;
  set have;
  mnth=month(datepart(orderdate));
run;
proc sort data=want out=want_month nodupkey;
  by mnth;
run;

And sum up:

proc sql;
  create table WANT_SUM as
  select  year(datepart(ORDERDATE)) as YR,
              month(datepart(ORDERDATE)) as MNTH,
              sum(TOTALAMOUNT) as TOT
  from    HAVE
  group by year(datepart(ORDERDATE)),
              month(datepart(ORDERDATE));
quit;

[RW9]

Then my second code should work fine, although I second @Kurt_Bremser in that using the calculated values would make the code smaller.    Also, Data is the plural, there is no such thing as "datas" 🐵

[Kurt_Bremser]

Try this:

proc sql;
create table want as
select
  year(datepart(orderdate)) as year,
  month(datepart(orderdate)) as month,
  sum(totalamount) as sales
from have
group by calculated year, calculated month
;
quit;

[Kurt_Bremser]

Add a suitable where condition in my proc sql code.

[RW9]

Sub-query the data first:

proc sql;
  create table WANT_SUM as
  select  YR,
          MNTH,
          sum(TOTALAMOUNT) as TOT
  from    (select year(datepart(ORDERDATE)) as YR,
                  month(datepart(ORDERDATE)) as MNTH,
                  TOTALAMOUNT
           from   HAVE
           where calculated YR=2008)
  group by YR,MNTH;
quit;

You could also do it using a "having" clause, though I prefer the sub-query.  You can also do it in datastep:

data inter;
  set have (where=(year(datepart(orderdate))=2008);
  yr=2008;
  mnth=month(datepart(orderdate));
run;

data want;
  set inter;
  by mnth;
  retain tot;
  tot=ifn(first.mnth,0,tot+totalamount);
run; 

[ballardw]

First a comment on data sources that force datetime values when there is no actual time component: find out who is doing this and teach them some manners.

One approach would be a custom format as SAS does not supply a Monname or equivalent format for datetime values.

Filtering for year is likely easiest by using:

where year(datepart(orderdate)) = 2008 if the value is datetime or

where year(orderdate)-2008  if the value is a date.

If your process never needs the time component it may be easier to just change the variable type:

Data want;

   set have;

   orderdate = datepart(orderdate);

run;

will make order date an actual date value. Then you can could use a format in report procedures to group by month.

proc tabulate data= have;

   where year(orderdate)=2008;

    class orderdate;

    format orderdate MONNAME3. ;

    var totalamount;

    table orderdate,

             totalamount*sum='';

run;