BookmarkSubscribeRSS Feed
Aliya-begum
Calcite | Level 5

data vs;

input pt  test$  result;

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

run;

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Diamond | Level 26

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;

 

--
Paige Miller
kiranv_
Rhodochrosite | Level 12

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
Tourmaline | Level 20

@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_
Rhodochrosite | Level 12

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

novinosrin
Tourmaline | Level 20

 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;
novinosrin
Tourmaline | Level 20

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
Super User

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
Calcite | Level 5

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
Tourmaline | Level 20

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
Quartz | Level 8


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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 14 replies
  • 1356 views
  • 0 likes
  • 7 in conversation