11-29-2016 10:04 AM
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;
11-29-2016 10:14 AM
Then my second code should work fine, although I second @KurtBremser in that using the calculated values would make the code smaller. Also, Data is the plural, there is no such thing as "datas" )
11-29-2016 10:10 AM
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;
11-29-2016 11:42 AM
11-29-2016 11:55 AM
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,
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);
11-29-2016 10:12 AM
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:
orderdate = datepart(orderdate);
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;
format orderdate MONNAME3. ;