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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.