topic TIme in SAS Programming

TIme

Boa — Mon, 05 Dec 2016 10:09:24 GMT

Please delete this thread.

Re: Need help in Date function

RW9 — Tue, 29 Nov 2016 15:04:42 GMT

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;

Re: Need help in Date function

Boa — Mon, 05 Dec 2016 10:10:48 GMT

Re: Need help in Date function

Kurt_Bremser — Tue, 29 Nov 2016 15:10:31 GMT

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;

Re: Need help in Date function

ballardw — Tue, 29 Nov 2016 15:12:21 GMT

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;

Re: Need help in Date function

RW9 — Tue, 29 Nov 2016 15:14:33 GMT

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

Re: Need help in Date function

Boa — Mon, 05 Dec 2016 10:09:52 GMT

Re: Need help in Date function

Kurt_Bremser — Tue, 29 Nov 2016 16:42:34 GMT

Add a suitable where condition in my proc sql code.

Re: Need help in Date function

RW9 — Tue, 29 Nov 2016 16:55:38 GMT

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;