data vs;
input pt test$ result;
cards;
101 SBP 150
101 SBP 152
102 DBP 48
102 DBP 52
;
run;
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??
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;
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;
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;
@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
you are right and thanks for correcting me. I have updated my answer
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;
you are right again. awesome points
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
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"?
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
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.