DATA Step, Macro, Functions and more

Do loop for mean every 500 rows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Do loop for mean every 500 rows

Hello All,

I have a data set of 3,924 rows and I need to get mean every 500 rows.  So, I need the mean for row 1 to 500, then 501-1000, 1001 to 1500 and so on.  I have been working on the following codes for a week now and still cannot figure it out.  Any suggestions? I appreciate and thank you for your time and help in advance.

Code 1:

%macro meanrating;

%do i=1 %to 3924;

from rec=i+1 to

rec=(i+1)*500;

proc means
data=work.query_for_nh_query3;

var overallrating;

%end;

%mend;

%meanrating

Code 2:

%MACRO mean;

%DO I = 1 %TO 3924 %by 500;

PROC MEANS
DATA=work.query_for_nh_query3;

VAR overallrating;

TITLE "Mean Every 500 Rows";

RUN;

%END;

%MEND;

%mean;

What am I doing wrong?  Thanks.


Accepted Solutions
Solution
‎04-29-2013 11:05 AM
Super User
Posts: 10,046

Re: Do loop for mean every 500 rows

Mark every 500 obs as a group, then using proc means .

data have;

set have;

if mod(_n_,500) =1 then group+1;

run;

proc means data=have;

by group;

var v;

output out=want mean=mean_v;

run;

View solution in original post


All Replies
Super Contributor
Posts: 418

Re: Do loop for mean every 500 rows

There are far more efficient ways of doing this in loop code, but a very simple way is to just break the dataset into X datasets, where X is divided by 500, and then compute your mean through that process. EXample'

Data First500 second500 third500 etc...;

set youroriginaldata;

if _n_ <=500 then output first500;

else if _n_ <=1000 then output second500;

etc...

run;

proc means data=first500;

var overallrating;

run;

Etc..

I would rather do this with a do loop and some if, then logic as it is far more efficient, but that depends on how repeatable you need this process to be and how large your original dataset is.

Respected Advisor
Posts: 3,799

Re: Do loop for mean every 500 rows

Posted in reply to Anotherdream
data class5v /view=class5v;
   set sashelp.class;
   group + mod(_n_,5) eq 1;
  
run;
proc means;
  
class group;
   run;
Respected Advisor
Posts: 3,156

Re: Do loop for mean every 500 rows

How about just using some data step:

data want;

  set work.query_for_nh_query3;

  array t(0:499) _temporary_;

t(mod(_n_,500))=overallrating;

  if mod(_n_,500)=0 then do;

  mean_500=mean(of t(*));

  output;

  /*call missing (of t(*));*/

  end;

keep mean_500;

run;

Haikuo

Solution
‎04-29-2013 11:05 AM
Super User
Posts: 10,046

Re: Do loop for mean every 500 rows

Mark every 500 obs as a group, then using proc means .

data have;

set have;

if mod(_n_,500) =1 then group+1;

run;

proc means data=have;

by group;

var v;

output out=want mean=mean_v;

run;

Occasional Contributor
Posts: 7

Re: Do loop for mean every 500 rows

Thank you all so much for your help, they all ended up providing me with the result I wanted and greatly enhanced my knowledge of SAS.  I really appreciate the help you all provided.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 250 views
  • 14 likes
  • 5 in conversation