Help using Base SAS procedures

Frequency by date range

Reply
Frequent Contributor
Posts: 117

Frequency by date range

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?

Regular Contributor
Posts: 233

Frequency by date range

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;

Frequent Contributor
Posts: 117

Re: Frequency by date range

I think you misundertood.

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

Regular Contributor
Posts: 233

Frequency by date range

Do you nweed total of num per month?

Contributor
Posts: 37

Frequency by date range

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;

Super Contributor
Posts: 1,636

Re: Frequency by date range

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

Super Contributor
Posts: 1,636

Re: Frequency by date range

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

Regular Contributor
Posts: 233

Frequency by date range

Linlin - I was about post the same logic you are ahead of me Smiley Happy. 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.

Super Contributor
Posts: 1,636

Frequency by date range

Hi Hima,

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

Have a nice weekend! - Linlin

Respected Advisor
Posts: 4,919

Frequency by date range

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

PG
Super Contributor
Posts: 1,636

Re: Frequency by date range

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

Respected Advisor
Posts: 3,156

Re: Frequency by date range

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

Super User
Posts: 19,770

Frequency by date range

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.

Ask a Question
Discussion stats
  • 12 replies
  • 1140 views
  • 0 likes
  • 7 in conversation