Contributor
Posts: 21

# TIme

[ Edited ]

Super User
Posts: 9,840

## Re: Need help in Date function

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;```
Contributor
Posts: 21

[ Edited ]

Super User
Posts: 9,840

## Re: Need help in Date function

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" )

Super User
Posts: 10,571

## Re: Need help in Date function

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;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

[ Edited ]

Super User
Posts: 10,571

## Re: Need help in Date function

Add a suitable where condition in my proc sql code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,840

## Re: Need help in Date function

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; ```

Super User
Posts: 13,941

## Re: Need help in Date function

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;

Discussion stats
• 8 replies
• 359 views
• 0 likes
• 4 in conversation