topic Re: How to perform calculation on every nth observation within an array? in SAS Programming

How to perform calculation on every nth observation within an array?

Errant — Fri, 01 Jun 2018 23:39:28 GMT

I have a data set that breaks down the monthly average price of three products over the course of 10 years. I need to represent the yearly average price for each of the three products, so I essentially to take the overall average of every 12 observations for 396+ observations. I'm trying to write an array that will do this. I know that there's something very off structurally, but I'm having a hard time seeing it myself. I've created three variables (gas_yearly etc.) but I've hit a dead end when it comes to writing the looping statement to average every 12 observations.

data want;
set qfour.aveprices;
input gas_yearly milk_yearly eggs_yeary;
array avg(3)gas_yearly milk_yearly eggs_yeary;
	 do i=1 to dim(avg);
	 *a statement that represents the overall average of every 12 observations

Thank you!

-e

Re: How to perform calculation on every nth observation within an array?

PaigeMiller — Sat, 02 Jun 2018 00:10:56 GMT

So here's the problem. Arrays work on variables, going across the rows, and not down a column, which seems to be what you are describing. Thus, as you have explained the problem, arrays will not work (or you have explained it in such a way that I'm not understanding the problem).

However, if you want to find the means of every sequence of 12 observations, here is how to do this. Create an index indicating which group of 12 observations each observation is in. This assumes that there is no variable indicating YEAR that is already in the data set. (And if there is not, then there should be). Then run PROC SUMMARY on this index variable.

Data want;
    set qfour.aveprices;
    group_of_twelve=floor((_n_-1)/12);
run;

Proc summary data=want;
    class group_of_twelve;
    var gas_yearly milk_yearly eggs_yeary; 
    /* That's your spelling error, not mine */
    output out=final mean=;
run;

Re: How to perform calculation on every nth observation within an array?

SASKiwi — Sat, 02 Jun 2018 00:29:43 GMT

Why don't you add a date column to your data to record the date of the measurements? It will make your analyses so much easier.

Re: How to perform calculation on every nth observation within an array?

Reeza — Sat, 02 Jun 2018 01:25:51 GMT

Can you explain your data structure and provide some sample data, fake data is fine.

What happens if your data is sorted? Can you sort the data to not take the 12th row and instead group things together more logically?

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 03:53:07 GMT

I've been issues sorting the data with two conditions. Here's a snippet of the original:

It goes from the years 2004-2014 for each product. Everything is already sorted, I'm having issues grouping and subsetting.

this code works:
proc print data = qfour.aveprices;
where year = 2004;run; 

this code does not for some reason.
proc print data = qfour.aveprices;
where year = 2004 and commodity = 'gas';run;

this also doesn't work

proc print data = qfour.aveprices;
where commodity = 'gas';

I get this response: NOTE: No observations were selected from data set QFOUR.AVEPRICES. NOTE: There were 0 observations read from the data set QFOUR.AVEPRICES. WHERE (commodity='gas');

I think I need to create a dataset for each product and then perhaps I can create a variable that calculates the average of every 12 obs.

Also trying to do the normal data step, where I create an output hasn't worked either.

  DATA sample_small;
  SET test.aveprices;
     IF year = 2014;
     If commodity = gas;
     RUN;
Proc print sample_small;run;
NOTE: Variable gas is uninitialized.
NOTE: There were 396 observations read from the data set TEST.AVEPRICES.
NOTE: The data set WORK.SAMPLE_SMALL has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.23 seconds
      cpu time            0.21 seconds


2614  proc print data= sample_small;run;

Re: How to perform calculation on every nth observation within an array?

Reeza — Sat, 02 Jun 2018 04:23:04 GMT

It’s Gas, capital G.

Try the following, see the want data set that gets created.

Proc means data=have;
Class year commodity;
Var price;
Output out =want mean= avg_price;
Run;

Re: How to perform calculation on every nth observation within an array?

s_lassen — Sat, 02 Jun 2018 07:22:51 GMT

Not quite sure if this is what you want to do, but if you want the rolling average of the last 12 observations, it can be done something like this:

data want;
  set qfour.aveprices;
  array a_gas (0:11) 8 _temporary_;
  array a_milk (0:11) 8 _temporary_;
  array a_eggs (0:11) 8 _temporary_;
  _N_=mod(_N_,12); /* array index */
  a_gas(_N_)=gas;
  a_milk(_N_)=milk;
  a_eggs(_N_)=eggs;
  gas_mean=mean(of a_gas(*));
  milk_mean=mean(of a_milk(*));
  eggs_mean=mean(of a_eggs(*));
run;

Note the use of zero-based arrays (the 0:11construct). They make the code simpler (array index is simply mod(_N_,12)) and execute slightly faster (simpler calculation of address offset).

Re: How to perform calculation on every nth observation within an array?

Patrick — Sat, 02 Jun 2018 08:26:12 GMT

@s_lassen

For the special case here where we've got already the month variable AND assuming there are no missing months, below could work as well.

data have;
  length Year Month 8 Commodity $4 Price 8;
  do commodity='Gas ', 'Milk', 'Eggs';
    do year=2004 to 2014;
      do month=1 to 12;
        price+2;
        output;
      end;
    end;
  end;
  stop;
run;

proc sort data=have;
  by Commodity year month;
run;

data want;
  set have;
  by Commodity year month;
  array _price {12} _temporary_;
  if first.commodity then
    do;
      call missing(of _price[*]);
    end;
  _price[month]=price;
  price_avg=mean(of _price{*});
run;

Re: How to perform calculation on every nth observation within an array?

PaigeMiller — Sat, 02 Jun 2018 11:08:13 GMT

Now that we've seen the data, @Reeza has come up with the simplest and most straight forward solution, use PROC MEANS/PROC SUMMARY.

I guess I don't understand why people are using a data step to solve this problem of finding an average of these data.

To @Errant, who said:

I think I need to create a dataset for each product and then perhaps I can create a variable that calculates the average of every 12 obs.

No you don't need any of this. One dataset will work, then run PROC MEANS/PROC SUMMARY on the data, shown in the reply above by Reeza.

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 13:38:25 GMT

thank you, that worked! I winded up using that code to create two outputs, one that shows the yearly average for product and the other that shows the overall average for commodities. I need to merge these two datasets, to show the yearly and monthly average per product, but I don't have a unique identifier, the only thing I have is commodity, and sort and merging by commodity doesn't seem to work. Do you know if there's a way to get around only having 2 classes in a class statement?

Re: How to perform calculation on every nth observation within an array?

PaigeMiller — Sat, 02 Jun 2018 13:40:19 GMT

@Errant wrote:

thank you, that worked! I winded up using that code to create two outputs, one that shows the yearly average for product and the other that shows the overall average for commodities. I need to merge these two datasets, to show the yearly and monthly average per product, but I don't have a unique identifier, the only thing I have is commodity, and sort and merging by commodity doesn't seem to work. Do you know if there's a way to get around only having 2 classes in a class statement?

There's really no way for me to advise you, unless you show us what you are talking about, and show us what you want.

You can have as many variables in a CLASS statement as you want.

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 14:07:28 GMT

So at this point in time I have two outputs.

I essentially want an output that expresses the product average for both month and year, by utilizing these outputs. However, I'm confused about how to sort/merge it. And the class statement runs with three classes, but it only produces one average, which makes me that maybe it only works on one dependent variable (avg_price in this case)?

Here's the code I tried.

Proc means data=qfour.aveprices;
Class year commodity;
Var price;
Output out =want mean= avg_price;
Run;

proc means data = qfour.aveprices;
Class month commodity;
var price;
output out = want2 mean =avg_price; run; 

*following only produces one average;
proc means data = qfour.aveprices;
Class month year commodity;
var price;
output out = want3 mean =avg_price; run;

I think I need to create two separate variables, one that represents yearly average and the other that represents monthly average.

Re: How to perform calculation on every nth observation within an array?

PaigeMiller — Sat, 02 Jun 2018 15:36:28 GMT

You have two separate data sets. I see that. Now explain them, what are they? How do you want to combine them? Show us, type in a portion of what you would like to see when you combine these two data sets.

I essentially want an output that expresses the product average for both month and year, by utilizing these outputs.

This is the first time you have mentioned that you want an average BY MONTH. Explain further where this month average would come from given your original data that you showed.

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 16:18:52 GMT

Sorry about the confusion, the monthly calculation was just the next calculation that I needed to complete after getting the yearIy average. After getting both of those in two separate outputs, I wanted to merge them, to get a table that shows the the product name, the yearly average and the monthly average, essentially combining the averages from both outputs.

At this point, I've tried sorting each output by commodity name (seemed like the closest thing to an identifier in both outputs).

I used this code to sort

data sorta;
set want;
by commodity;run;
proc print data = sorta;run;

*and then for the second output
data sortb;
set want2;
by commodity;run;
proc print data = sortb;run;

I get the same output for both commands for some reason. And it doesn't state any errors, but as you can see it's missing data. Which is messing up the merge process.

Ultimately, I would like a data set that looks like the one above with the commodity and the average price for year and month.

Re: How to perform calculation on every nth observation within an array?

PaigeMiller — Sat, 02 Jun 2018 18:53:00 GMT

@Errant wrote:

Sorry about the confusion, the monthly calculation was just the next calculation that I needed to complete after getting the yearIy average. After getting both of those in two separate outputs, I wanted to merge them, to get a table that shows the the product name, the yearly average and the monthly average, essentially combining the averages from both outputs.

I don't think you really want to "merge" the two data sets ... not MERGE in the sense of the SAS data step command MERGE. Or maybe you do mean "merge", but I can't imagine what that table would look like. Please show us an example (type it in yourself) of what the merged table looks like.

I also don't know what final result of all this analysis should be, after you do the merge somehow, then what? Please show us. There may be very simple ways to get there, we might be able to figure that out, if only you told us what final analysis should look like.

I think we need to temporarily stop talking about SAS code, and talk about EXACTLY what output you need and where you are going, and the only way to do that is for you to show us what you want.

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 19:35:25 GMT

So I've reviewed the original requirements and I misinterpreted the requirements. Ultimately I do need to combine the month output with the yearly average output, but the month output actually needs to look like this:

So technically I'm not calculating an average for the month output. I'm confused about how to get this from the original dataset though.

Re: How to perform calculation on every nth observation within an array?

Reeza — Sat, 02 Jun 2018 20:01:13 GMT


*Sort by month/year;
proc sort data=have;
by month year;
run;

*calculate summary statistics by month;
proc means data=have nway;
by month;
var gas milk eggs;
output out=monthly_averages mean(gas)= avg_gas mean(milk) = avg_milk mean(eggs)=avg_eggs;
run;

*merge results back into original dataset by month;
data want;
merge have monthly_averages;
by month;
run;

If you post data as text instead of image that would be helpful. We can't write a program off an image, unless we type it out and I'm too lazy for that. This will calculate monthly averages across all years, if you only need certain years, you can use a WHERE statement to filter the years. Then it merges it back to the original data set.

Re: How to perform calculation on every nth observation within an array?

Errant — Sat, 02 Jun 2018 20:07:00 GMT

There a lot of observations, but I can upload the datafile.

Re: How to perform calculation on every nth observation within an array?

Reeza — Sun, 03 Jun 2018 00:50:03 GMT

Did the code provided work? If there’s an issue I’ll take another look but it should work. If it doesn’t include your code, log and a description of the issue.

Re: How to perform calculation on every nth observation within an array?

Errant — Sun, 03 Jun 2018 01:00:19 GMT

I actually had to switch gears for a couple hours, but I'm going get back to that specific problem. I'll definitely keep you updated, thank you!