Frequency by date range

[vomer]

Hi guys,

I have a dataset that has 2 columns :

1) Date and time

2) num column

What I want to do in sas is find all values that fall between Jan 1 - Jan 31 2012 and for those values add/total the numbers in the num column.

How would I go about this?

[Hima]

data have;
input date num 3.;
informat date mmddyy8.;
cards;
01/8/2012 9
02/2/2012 5
01/7/2012 9
03/2/2012 8
01/31/2012 10
01/30/2012 6
;
run;

proc print; run;

data want;
set have;
format date mmddyy10.
    new_col mmddyy10.
;
if '1JAN2012'd < date < '1FEB2012'd then new_col = date+num;
run;

proc print; run;

[vomer]

I think you misundertood.

I have date/time values already. Just need to add up values in num column by monthly range.

[Hima]

Do you nweed total of num per month?

[akberali67]

data have;

input date num 3.;

informat date mmddyy8.;

cards;

01/8/2012 9

02/2/2012 5

01/7/2012 9

03/2/2012 8

01/31/2012 10

01/30/2012 6

;

run;

data have1;

set have;

if '1JAN2012'd < date < '1FEB2012'd;

monthly_range = month(date);

run;

proc summary data = have1 nway missing;

class monthly_range;

var num_column;

output out = have2 sum=;

run;

Remove nway missing if you want overall total too.

Make sure that the num_colum is numeric. If not then add then use this:

data have1;

set have;

if '1JAN2012'd < date < '1FEB2012'd;

monthly_range = month(date);

num_colum1 = input(num_column, best32.)

run;

proc summary data = have1 nway missing;

class monthly_range;

var num_column1;

output out = have2 sum=;

run;

[Linlin]

borrowed data from Hima and code from PGstate:

data have;

input date num 3.;

informat date mmddyy8.;

cards;

01/8/2012 9

02/2/2012 5

01/7/2012 9

03/2/2012 8

01/31/2012 10

01/30/2012 6

;

run;

proc sql;

create table periods as select distinct

intnx("month",date,0,"beginning") as period format=mmyyd7.,

intnx("day",date,0,"beginning") as periodBeg format=mmddyy10.,

intnx("day",date,0,"end") as periodEnd format=mmddyy10.

from have;

create table want as

select period, sum(num) as total

from periods inner join have on date between periodBeg and periodEnd

group by period;

drop table periods;

quit;

proc print data=want;run;

                                   Obs     period    total

                                      1     01-2012      34

                                      2     02-2012       5

                                      3     03-2012       8

Linlin

[Linlin]

or

data have;

input date num 3.;

informat date mmddyy8.;

cards;

01/8/2012 9

02/2/2012 5

01/7/2012 9

03/2/2012 8

01/31/2012 10

01/30/2012 6

;

run;

proc sql;

create table want as

      select  intnx("month",date,0,"beginning") as month format=mmyyd7.

          ,sum(num) as total

                         from have

                            group by month;

quit;

Updated after PG's comments

[Hima]

Linlin - I was about post the same logic you are ahead of me . I love the way you, Ksharp, Art, Tom, Hai.Kuo code. Learned a lot of stuff from you guys. The word "THANKS" is not enough.

[Linlin]

Hi Hima,

Thank you for your encouragement! We learn from each other.

Have a nice weekend! - Linlin

[PGStats]

Linlin, I consider this is the best way to do it. The distinct option is not required however, uniqueness is already provided by the group by clause.

Cheers!

PG

[Linlin]

Thank you PG! I have updated the code.  - Linlin

[Haikuo]

I know I cheated. Well, you can always add 'month' by using another data step:

data have;

input date num 3.;

informat date mmddyy8.;

month=put(date, yymmn.);

cards;

01/8/2012 9

02/2/2012 5

01/7/2012 9

03/2/2012 8

01/31/2012 10

01/30/2012 6

;

run;

proc sql;

create table want as

select month ,sum(num) as total

from have

group by month

order by month;

quit;

proc print;run;

Haikuo

[Reeza]

If you're always looking for monthly totals you can use proc means with the appropriate format on your date variable. If you have a datetime variable the format you probably want is dtmonyy7.

ie

proc means data=sashelp.citiday n sum;

  class date;

  format date monyy7.;

  var snysecm;

  output out=sample n=count sum=total;

run;

Except in yours use dtmonyy7. because you have a datetime variable instead of a date variable.