Solved
Contributor
Posts: 44

# Summing variables in a dataset by date

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.

Accepted Solutions
Solution
‎01-18-2012 02:35 PM
Super Contributor
Posts: 1,636

## Re: Summing variables in a dataset 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

All Replies
Posts: 4,736

## Summing variables in a dataset by date

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

Contributor
Posts: 44

## Summing variables in a dataset by date

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

PROC Star
Posts: 8,163

## Summing variables in a dataset by date

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.

Contributor
Posts: 44

## Summing variables in a dataset by date

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

PROC Star
Posts: 8,163

## Summing variables in a dataset by date

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

Contributor
Posts: 44

## Summing variables in a dataset by date

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?

Solution
‎01-18-2012 02:35 PM
Super Contributor
Posts: 1,636

## Re: Summing variables in a dataset 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

Contributor
Posts: 44

## Re: Summing variables in a dataset by date

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

Super Contributor
Posts: 1,636

## Summing variables in a dataset by date

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

🔒 This topic is solved and locked.