turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Do loop for mean every 500 rows

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 10:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 11:05 AM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 10:56 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 11:15 AM

set sashelp.class;

group + mod(_n_,

class group;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 10:57 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 11:05 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-29-2013 11:16 AM

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.