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?
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;
I think you misundertood.
I have date/time values already. Just need to add up values in num column by monthly range.
Do you nweed total of num per month?
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;
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
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
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.
Hi Hima,
Thank you for your encouragement! We learn from each other.
Have a nice weekend! - Linlin
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
Thank you PG! I have updated the code. - Linlin
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.