topic Summing variables in a dataset by date in SAS Procedures

Summing variables in a dataset by date

carbs — Wed, 18 Jan 2012 09:50:33 GMT

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.

Summing variables in a dataset by date

Patrick — Wed, 18 Jan 2012 11:46:26 GMT

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

Summing variables in a dataset by date

carbs — Wed, 18 Jan 2012 13:14:37 GMT

Tried the code above with slight changes:

data test;

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

Summing variables in a dataset by date

art297 — Wed, 18 Jan 2012 13:23:13 GMT

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.

Summing variables in a dataset by date

Linlin — Wed, 18 Jan 2012 13:25:43 GMT

change "do while (Date<'2011-01'd)" to "do while (Date<mdy(1,1,2011));

Summing variables in a dataset by date

carbs — Wed, 18 Jan 2012 14:51:33 GMT

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

Summing variables in a dataset by date

art297 — Wed, 18 Jan 2012 14:59:09 GMT

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".

Summing variables in a dataset by date

carbs — Wed, 18 Jan 2012 19:11:34 GMT

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?

Re: Summing variables in a dataset by date

Linlin — Wed, 18 Jan 2012 19:35:48 GMT

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

Re: Summing variables in a dataset by date

carbs — Wed, 18 Jan 2012 21:08:29 GMT

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