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

Hi,

This seems to be a fairly easy problem to solve, but for some reason I cannot think of a way to solve it.

I have four macro variables, StartDateMonth, StartDateYear, EndDateMonth and EndDateYear that the user enters in.

The variable names in my data set are in the form of Rate_01_2010, Rate_02_2010, Rate_03_2010, Rate_4_2010, ...., Rate_12_2012. I'd like to calculate the average of the rates based on the dates that the user enters.

Example:

%Let StartDateMonth = 6;

%Let StartDateYear = 2010;

%Let EndDateMonth = 3;

%Let EndDateYear = 2012;

Basically, I would want the calculation

     Average = mean(Rate_06_2010, Rate_07_2010, ....., Rate_02_2012, Rate_03_2012);

However, I'd like this to be done automatically, based on the inputs.

Thank you for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Maybe this?

%macro RateVars(sm, sy, em,ey);
   %local sdate edate d m y vars;
   %let sdate = %sysfunc(mdy(&sm,1,&sy));
   %let edate = %sysfunc(mdy(&em,1,&ey));
   %let d = &sdate;
   %let vars = ;
   %do %while (&d le &edate);
      %let m = %sysfunc(putn(%sysfunc(month(&d)),z2.));
      %let y = %sysfunc(putn(%sysfunc(year(&d)),f4.0));
      %let vars = &vars Rate_&m._&y;
      %let d = %sysfunc(intnx(Month,&d,1));
   %end;
   &vars
%mend;

Use in data step as:

data new;
set have;
average = mean(of %RateVars (&StartDateMonth,&StartDateYear,&EndDateMonth,&EndDateYear))  ;
run;

View solution in original post

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

try:

Average = mean(Rate_&StartDateMonth._&StartDateYear, Rate_%eval(&StartDateMonth+1.)_&StartDateYear,

Rate_%eval(&EndDateMonth.-1)._&EndDateYear, Rate_&EndDateMonth._&EndDateYear);

it will not work at the beginning and the end of a year.

AncaTilea
Pyrite | Level 9

Hi,

One way could be to rearrange your data from wide-to-long, thus having one rate column and a date column.

Then you can use either proc sql or proc summary/means to summarize the rate (column) using a where statement that uses your macro variables, something like this:

data long;

     set your_wide_data;

     rate = rate_03_2010;

     date = mdy(03,01,2010);*I chose the day to be the first (01) since it doesn't seem to matter for your problem;

     output;

     rate = rate_04_2010;

     date = mdy(06,01,2010);

     output;

...

     rate = rate_06_2012;

     date = mdy(06,01,2012);

     output;

proc means data = long;

     var rate;

     where mdy(&StartDateMonth., 01, &StartDateYear. ) <= date <= mdy(&EndDateMonth.,01, &endDateYear.);

run;

or

proc sql noprint;

     create table averages_&StartDateMonth as

     select avg(rate) as average_rate

     from long

     where mdy(&StartDateMonth., 01, &StartDateYear. ) <= date <= mdy(&EndDateMonth.,01, &endDateYear.);

quit;

Best of luck,

Anca.

NickR
Quartz | Level 8

You may try below code and see if it works for you. Good Luck.

data _null_;

  length varlist $1000;

  retain varlist;

  sdate = input(compress("&StartDateYear"||'-'||put(input("&StartDateMonth",best.),z2.)||'-01'),yymmdd10.);

  edate = input(compress("&EndDateYear"||'-'||put(input("&EndDateMonth",best.),z2.)||'-01'),yymmdd10.);

  do dates = sdate to edate;

  if day(dates)=1 then do;

  month = put(month(dates),z2.);

  year = put(year(dates),4.);

  if varlist='' then varlist = 'Rate_'||strip(month)||'_'||strip(year);

  else varlist = strip(varlist)||', '||'Rate_'||strip(month)||'_'||strip(year);

  output;

  end;

  end;

  call symput('varlist',varlist);

  format sdate edate dates date9.;

run;

%put &varlist;

data output;

  Average = mean(&varlist);

run;

Astounding
PROC Star

If you're lucky, this will be easy.  "Lucky" here means that the rate variables are internally stored in date order with no other variables in between.  PROC CONTENTS will show you that.  In that case, you could just use:

average = mean(of rate_&StartDateMonth._&StartDateYear -- rate_&EndDateMonth._&EndDateYear);

If the variables are in order, but they are interspersed with character variables, it is still possible:

average = mean(of rate_&StartDateMonth._&StartDateYear -numeric- rate_&EndDateMonth._&EndDateYear);

ballardw
Super User

Maybe this?

%macro RateVars(sm, sy, em,ey);
   %local sdate edate d m y vars;
   %let sdate = %sysfunc(mdy(&sm,1,&sy));
   %let edate = %sysfunc(mdy(&em,1,&ey));
   %let d = &sdate;
   %let vars = ;
   %do %while (&d le &edate);
      %let m = %sysfunc(putn(%sysfunc(month(&d)),z2.));
      %let y = %sysfunc(putn(%sysfunc(year(&d)),f4.0));
      %let vars = &vars Rate_&m._&y;
      %let d = %sysfunc(intnx(Month,&d,1));
   %end;
   &vars
%mend;

Use in data step as:

data new;
set have;
average = mean(of %RateVars (&StartDateMonth,&StartDateYear,&EndDateMonth,&EndDateYear))  ;
run;

stat11
Calcite | Level 5

Thank you! That works perfectly!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 3103 views
  • 9 likes
  • 6 in conversation