topic Re: i have a data from which i need average for 101 and 102 and a flag populated for average values. in SAS Programming

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

Aliya-begum — Fri, 23 Nov 2018 14:33:10 GMT

data vs;

input pt test$ result;

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

run;

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

RW9 — Fri, 23 Nov 2018 14:37:22 GMT

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??

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

Kurt_Bremser — Fri, 23 Nov 2018 14:38:51 GMT

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"?

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

Aliya-begum — Fri, 23 Nov 2018 15:14:39 GMT

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

I have a data set with three variables i need average of result varible and flag for average values

Aliya-begum — Fri, 23 Nov 2018 15:36:35 GMT

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

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

RW9 — Fri, 23 Nov 2018 15:41:00 GMT

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;

Re: I have a data set with three variables i need average of result varible and flag for average val

novinosrin — Fri, 23 Nov 2018 15:52:53 GMT


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;

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

kiranv_ — Fri, 23 Nov 2018 16:59:50 GMT

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;

Re: I have a data set with three variables i need average of result varible and flag for average val

saslove — Fri, 23 Nov 2018 16:28:59 GMT

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;

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

novinosrin — Fri, 23 Nov 2018 16:32:53 GMT

@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

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

kiranv_ — Fri, 23 Nov 2018 16:43:41 GMT

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

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

novinosrin — Fri, 23 Nov 2018 16:57:46 GMT

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;

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

kiranv_ — Fri, 23 Nov 2018 16:59:15 GMT

you are right again. awesome points

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

novinosrin — Fri, 23 Nov 2018 17:07:18 GMT

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

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

PaigeMiller — Sat, 24 Nov 2018 14:14:13 GMT

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;