i have a data from which i need average for 101 and 102 and a flag populated for average values.

[Aliya-begum]

data vs;

input pt  test$  result;

cards;
101 SBP 150
101 SBP 152
102 DBP 48
102 DBP 52
;

run;

[RW9]

You need to be clearer in your post.  Show what you want out for instance.  I can guess that proc means will give you the average:

proc means data=vs;
  class pt;
  var result;
  output out=want mean=mean;
run;

But I don't know about flags as there is not average in that data, nor would the output from above be in your data to flag??

[Aliya-begum]

As i have the data

data vs;
input pt test$ result;
cards;
101 SBP 150
101 SBP 152
102 DBP 48
102 DBP 52
;
run;

i need an output as given below

pt test result res_avg flag
101 SBP 150 150
101 SBP 152 152
101 SBP 151 y

102 DBP 48 48
102 DBP 52 52
102 DBP 50 y

[RW9]

Right, so do the means step, then set that with your data e.g.

proc means data=vs;
  class pt;
  var result;
  output out=want mean=mean;
run;

data vs;
  set vs (in=a) mean (in=b);
  if b then flag="Y";
run;

[PaigeMiller]

This is a long thread, and I am jumping in late, but I highly recommend the solution by @RW9. You want to use PROC MEANS or PROC SUMMARY to do the computation of statistics across groups, that is one of the features of PROC MEANS/PROC SUMMARY, plus it handles missing values properly, plus it has been tested, debugged and validated in a bazillion, 408 thousand and three real-world applications.

You do not want to write your own data step code to compute group means. Not only is this unnecessarily time consuming to write and test this code compared to using the features in PROC MEANS/PROC SUMMARY, but writing your own code also runs the risk of bugs and incorrect answers (which is not the case with using PROC MEANS/PROC SUMMARY).

---

@RW9 wrote:

Right, so do the means step, then set that with your data e.g.

proc means data=vs;
  class pt;
  var result;
  output out=want mean=mean;
run;

data vs;
  set vs (in=a) mean (in=b);
  if b then flag="Y";
run;

---

[kiranv_]

something like below may work

data prefinal (drop = tot count);
do until(last.pt);
set vs;by pt;
tot= sum(tot, result);
count=sum(count,1);
avg=tot/count;
flag = 'Y';
end;
result = .;
run;

data want;
set vs prefinal;
by pt;
run;

you can also proc sql as

proc sql;
create table want as
select pt, test, result, . as avg, ' ' as flag from vs
union
select distinct  pt, test, . as result, mean(result) as avg, "Y" as flag
from vs
group by pt;

[novinosrin]

@kiranv_  Your datastep  logic will not work if there are more than 2 records for each id and I believe that's kind of case in business is not likely

[kiranv_]

you are right and thanks for correcting me. I have updated my answer

[novinosrin]

 Still you need to change your append to rather interleave and conditionally assign avg

data want1;
set vs prefinal;
by pt;
/*write the condition here*/
run;

[kiranv_]

you are right again. awesome points

[novinosrin]

I purposely didn't write the condition although gave you the exquisite corrections 🙂  for both your steps

hint:use in= for your condition

PLus your sql is not perfect either requires some correction/modification

[Kurt_Bremser]

The average can be done quite simply in SQL:

proc sql;
create table want as
select pt, test, avg(result) as result_avg
from vs
group by pt, test;
quit;

What value should end up in the "flag"?

[Aliya-begum]

data vs;
input pt test$ @15 result;
cards;
101 SBP 150
101 SBP 152
102 DBP 48
102 DBP 52
;
run;
proc print;
run;

i need output as given below:

  pt     test        result     res_avg    flag

101   SBP        150      150
101   SBP        152      152
101   SBP                    151                 y

102   DBP        48        48
102   DBP        52        52
102   DBP                    50                   y

[novinosrin]

data vs;
input pt test$  result;
cards;
101 SBP 150
101 SBP 152
102 DBP 48
102 DBP 52
;

data want;
s=0;
do _n_=1 by 1 until(last.pt);
set vs;
by pt;
res_avg=result;
s+result;
output;
end;
result=.;
res_avg=s/_n_;
flag='Y';
output;
drop s;
run;

[saslove]

proc means data=sashelp.class ;
class sex ;
var height;
output out=want mean=mean;
run;

data vs;
set want (in=a) sashelp.class (in=b);
if sex not in ("");
if a then flag="Average";
if flag not in ("Average") then do; res_avg=height; end;
run;
proc sort data=vs;
by sex; run;