BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
carbs
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

9 REPLIES 9
Patrick
Opal | Level 21

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

carbs
Calcite | Level 5

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

art297
Opal | Level 21

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.

carbs
Calcite | Level 5

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 Smiley Happy

art297
Opal | Level 21

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

carbs
Calcite | Level 5

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?

Linlin
Lapis Lazuli | Level 10

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

carbs
Calcite | Level 5

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 Smiley Happy

Linlin
Lapis Lazuli | Level 10

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 9094 views
  • 6 likes
  • 4 in conversation