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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
Anotherdream
Quartz | Level 8

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.

data_null__
Jade | Level 19
data class5v /view=class5v;
   set sashelp.class;
   group + mod(_n_,5) eq 1;
  
run;
proc means;
  
class group;
   run;
Haikuo
Onyx | Level 15

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

Ksharp
Super User

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;

hsharma
Calcite | Level 5

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.

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!

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
  • 5 replies
  • 1049 views
  • 14 likes
  • 5 in conversation