I have a dataset with monthly observations of variable x for 10 countries between 2000-2010. How could I sum the monthly observations of countries together, so that the final dataset would contain only one variable (all countries' x values summed) between 2000-2010? I tried proc means sum, but couldn't manage to sum by date.
Are you looking for something like this?
data have;
input sum_dom date $;
cards;
0.05645 2000-01
0.07511 2000-01
0.08539 2000-01
0.06388 2000-01
0.06893 2000-01
0.14131 2000-01
0.08486 2000-01
0.2402 2000-01
0.04822 2000-01
0.05328 2000-01
0.05645 2000-02
0.07511 2000-02
0.08539 2000-02
0.06388 2000-02
0.06893 2000-02
0.14131 2000-02
0.08486 2000-02
0.2402 2000-02
0.04822 2000-02
0.05328 2000-02
;
data want;
set have;
by date;
if first.date then total=0;
total+sum_dom;
if last.date then output;
run;
proc print;run;
Linlin
data have (keep=Date value);
format date date9.;
Date='01jan2005'd;
do while (Date<'01dec2011'd);
do country=1 to 10;
value=ceil(ranuni(1)*10);
output;
end;
Date=intnx('month', Date, 1,'b');
end;
run;
proc means data=have noprint;
class Date;
var Value;
types Date;
output out=SumPerMonth(drop=_:) sum=SumPerMonth
;
run;
title 'Sum per Month';
proc print data=SumPerMonth noobs;
run;
HTH
Patrick
Tried the code above with slight changes:
set totalokkkk;
data test (keep=date value);
format date date6.;
Date='yymm6'd;
dowhile (Date<'2011-01'd);
do country=1to10;
value=ceil(ranuni(1)*10);
output;
end;
Date=intnx('month', Date, 1,'b');
end;
run;
procdata=test noprint; means
class Date;
var SMB_Dom;
types Date;
outputout=SumPerMonth(drop=_:) sum=SumPerMonth
;
run;
title'Sum per Month';
procdata=SumPerMonth noobs; print
run;
The error code received was related to the date part:
ERROR: Invalid date/time/datetime constant 'yymm6'd.
ERROR: Invalid date/time/datetime constant '2011-01'd.
ERROR 77-185: Invalid number conversion on '2011-01'd
Why did you change the form of the date constant? I'm not sure if the form you used is valid. It isn't one of the forms listed in the documentation: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000780334.htm
and, besides, the statment:
Date='yymm6'd;
is definitely incorrect.
I have the dates in my data in the following form: 2000-01, 2000-02 etc.. That's why I thought I could change the date form. I uploaded to the first post a sample of the data I have, so it includes much more stuff than mentioned also. I get SAS to sum some values with the code by Patrick, but I guess it sums all variables together, even though I just want the SMB_Dom to be summed....Thanks for the answers already, and patience
If you look at the actual values that are assigned to your dates, as long as they are SAS dates and only formatted to appear as year and month, you will see that they actually reflect the first of each month.
Second, your approach is correct regarding the sum. I didn't catch it initially, but you are summing a variable that you don't create in your example data. You only create a variable called "value", but then try to sum a variable called "SMB_Dom".
Good clarification relating to the dates, thanks art297! Somehow the whole approach of Patrick's code is a bit unclear for me, why it has to be done in such a complicated way..Since I already have the SMB_Dom variable according to dates, and would need to sum up countries' SMB_Dom values each month.. How should I change the code to be able to sum the variable SMB_Dom then?
Are you looking for something like this?
data have;
input sum_dom date $;
cards;
0.05645 2000-01
0.07511 2000-01
0.08539 2000-01
0.06388 2000-01
0.06893 2000-01
0.14131 2000-01
0.08486 2000-01
0.2402 2000-01
0.04822 2000-01
0.05328 2000-01
0.05645 2000-02
0.07511 2000-02
0.08539 2000-02
0.06388 2000-02
0.06893 2000-02
0.14131 2000-02
0.08486 2000-02
0.2402 2000-02
0.04822 2000-02
0.05328 2000-02
;
data want;
set have;
by date;
if first.date then total=0;
total+sum_dom;
if last.date then output;
run;
proc print;run;
Linlin
Thanks Linlin! Your code did the job. Perhaps my question wasn't that easy to understand at the beginning but I'm glad you had the time to help
change "do while (Date<'2011-01'd)" to "do while (Date<mdy(1,1,2011));
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.