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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.