Please delete this thread.
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;
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" 🐵
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;
Add a suitable where condition in my proc sql code.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.