topic Re: How to add the average value for each ID into the dataset in SAS Programming

How to add the average value for each ID into the dataset

knveraraju91 — Wed, 23 Mar 2016 21:55:01 GMT

I need your help.

I have data set with four variables; id test seq value

data&colon; 1 DBP 1 88

1 DBP 2 86

1 DBP 3 .

1 DBP 1 90

1 DBP 2 86

1 DBP 3 88

1 DBP 4 84

1 SBP 1 128

1 SBP 2 130

I need to calculate average by id and all the sequence values and include in my data

My output should look like:

id test seq value Type

1 DBP 1 88

1 DBP 2 88

1 DBP 3 .

1 DBP 88 average

1 DBP 1 90

1 DBP 2 86

1 DBP 3 88

1 DBP 4 84

1 DBP 87 average

1 SBP 1 128

1 SBP 2 130

1 SBP 129 average

my code is:

proc transpose data=one out=two(drop=_name_);
by test;
var value;
id seq;
run;

data three;
set two;
four=mean(_1,_2,_3,_4);
run;

proc transpose data=three out=four;
var _1 _2 _3 _4 four;
by test;
run;

I am getting expected results. But I want to know any other simple appraoch. Please advise.

Thanks

Re: How to add the average value for each ID into the dataset

Reeza — Wed, 23 Mar 2016 21:55:17 GMT

I don't know if it's simpler but you could use a DOW loop.

You could also use proc means and append results instead of transpose.

Re: How to add the average value for each ID into the dataset

LinusH — Wed, 23 Mar 2016 22:30:56 GMT

Please don't mix aggregate values with original data in the same data set.

Your layout is a report, use a reporting tool for this. Like PROC TABULATE or REPORT.

Re: How to add the average value for each ID into the dataset

knveraraju91 — Wed, 23 Mar 2016 23:39:44 GMT

what is the code for Proc means and append the results. Thank you

Re: How to add the average value for each ID into the dataset

Reeza — Thu, 24 Mar 2016 00:29:29 GMT

I highly highly recommend learning proc means.

You'll need to sort the results to get what you want but the following should get you started. It's obviously only a sketch of the code, again I highly recommend learning the proc. Here's a paper and you'll find many many more on lexjansen.com

http://www.lexjansen.com/nesug/nesug08/ff/ff06.pdf

proc means data=have;
by GROUPING_VARIABLE;
var ANALYSIS_VARIABLE;
output out=avg_value mean=NAME;
run;

data want;
set have avg_value;
run;