topic Frequency by date range in SAS Procedures

Frequency by date range

vomer — Fri, 30 Mar 2012 18:52:10 GMT

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?

Frequency by date range

Hima — Fri, 30 Mar 2012 19:07:07 GMT

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;

Re: Frequency by date range

vomer — Fri, 30 Mar 2012 19:16:15 GMT

I think you misundertood.

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

Frequency by date range

Hima — Fri, 30 Mar 2012 19:24:38 GMT

Do you nweed total of num per month?

Frequency by date range

akberali67 — Fri, 30 Mar 2012 19:33:29 GMT

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;

Re: Frequency by date range

Linlin — Fri, 30 Mar 2012 19:39:39 GMT

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

Re: Frequency by date range

Linlin — Fri, 30 Mar 2012 19:52:54 GMT

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

Re: Frequency by date range

Haikuo — Fri, 30 Mar 2012 19:53:39 GMT

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

Frequency by date range

Hima — Fri, 30 Mar 2012 19:57:06 GMT

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.

Frequency by date range

Linlin — Fri, 30 Mar 2012 20:01:18 GMT

Hi Hima,

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

Have a nice weekend! - Linlin

Frequency by date range

Reeza — Fri, 30 Mar 2012 20:24:07 GMT

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.

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.

Frequency by date range

PGStats — Fri, 30 Mar 2012 22:40:22 GMT

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!

Re: Frequency by date range

Linlin — Fri, 30 Mar 2012 23:22:47 GMT

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