DATA Step, Macro, Functions and more

TIme

Reply
Contributor Boa
Contributor
Posts: 21

TIme

[ Edited ]

Please delete this thread.

Super User
Super User
Posts: 7,981

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

Re: Need help in Date function

[ Edited ]
 
Super User
Super User
Posts: 7,981

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

Super User
Posts: 7,849

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

Re: Need help in Date function

[ Edited ]
Posted in reply to KurtBremser
 
Super User
Posts: 7,849

Re: Need help in Date function

Add a suitable where condition in my proc sql code.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,981

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: 11,343

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;

 

 

   

Ask a Question
Discussion stats
  • 8 replies
  • 310 views
  • 0 likes
  • 4 in conversation