Average by group

[vnreddy]

Hi,

DATA avg_score ;
INFILE DATALINES DLM="09"X DSD;
input USUBJID$1-8 VISIT$9-20 GROUP$21-30 TRT$31-40 avg_score 8.;
datalines
;
I0001-23 Week 1 RESPONSE Ap 6 mg 36.66666667
I0001-23 Week 1 NORESPONSE Ap 6 mg 26.66666667
I0001-23 Week 2 RESPONSE Ap 6 mg 8.333333333
I0001-23 Week 2 NORESPONSE Ap 6 mg 10
I0001-23 Week 3 RESPONSE Ap 6 mg 8.333333333
I0001-23 Week 3 NORESPONSE Ap 6 mg 10
I0004-27 Week 4 RESPONSE SM 6 mg 20.23809524
I0004-27 Week 4 NORESPONSE SM 6 mg 25.71428571
I0004-27 Week 6 RESPONSE SM 6 mg 51.19047619
I0004-27 Week 6 NORESPONSE SM 6 mg 63.14285714
I0004-27 Week 8 RESPONSE SM 6 mg 23.05555556
I0004-27 Week 8 NORESPONSE SM 6 mg 31.66666667
run;

I have below table as a source and I want to create two tables from this: 1) Total,  2) concatenate total with existing table. 

USUBJID	VISIT	GROUP	TRT	avg_score
I0001-23	Week 1	RESPONSE	Ap 6 mg	36.66666667
I0001-23	Week 1	NORESPONSE	Ap 6 mg	26.66666667
I0001-23	Week 2	RESPONSE	Ap 6 mg	8.333333333
I0001-23	Week 2	NORESPONSE	Ap 6 mg	10
I0001-23	Week 3	RESPONSE	Ap 6 mg	8.333333333
I0001-23	Week 3	NORESPONSE	Ap 6 mg	10
I0004-27	Week 4	RESPONSE	SM 6 mg	20.23809524
I0004-27	Week 4	NORESPONSE	SM 6 mg	25.71428571
I0004-27	Week 6	RESPONSE	SM 6 mg	51.19047619
I0004-27	Week 6	NORESPONSE	SM 6 mg	63.14285714
I0004-27	Week 8	RESPONSE	SM 6 mg	23.05555556
I0004-27	Week 8	NORESPONSE	SM 6 mg	31.66666667

expected output 1)

USUBJID	VISIT	GROUP	TRT	avg_score
I0001-23	Week 1	TOTAL	Ap 6 mg	31.66666667
I0001-23	Week 2	TOTAL	Ap 6 mg	9.166666667
I0001-23	Week 3	TOTAL	Ap 6 mg	9.166666667
I0004-27	Week 4	TOTAL	SM 6 mg	22.97619048
I0004-27	Week 6	TOTAL	SM 6 mg	57.16666667
I0004-27	Week 8	TOTAL	SM 6 mg	27.36111111

output 1: avg_score derived by averaging the response and noreponse by week wise.

expected output 2).

USUBJID	VISIT	GROUP	TRT	avg_score
I0001-23	Week 1	RESPONSE	Ap 6 mg	36.66666667
I0001-23	Week 1	NORESPONSE	Ap 6 mg	26.66666667
I0001-23	Week 1	TOTAL	Ap 6 mg	31.66666667
I0001-23	Week 2	RESPONSE	Ap 6 mg	8.333333333
I0001-23	Week 2	NORESPONSE	Ap 6 mg	10
I0001-23	Week 2	TOTAL	Ap 6 mg	9.166666667
I0001-23	Week 3	RESPONSE	Ap 6 mg	8.333333333
I0001-23	Week 3	NORESPONSE	Ap 6 mg	10
I0001-23	Week 3	TOTAL	Ap 6 mg	9.166666667
I0004-27	Week 4	RESPONSE	SM 6 mg	20.23809524
I0004-27	Week 4	NORESPONSE	SM 6 mg	25.71428571
I0004-27	Week 4	TOTAL	SM 6 mg	22.97619048
I0004-27	Week 6	RESPONSE	SM 6 mg	51.19047619
I0004-27	Week 6	NORESPONSE	SM 6 mg	63.14285714
I0004-27	Week 6	TOTAL	SM 6 mg	57.16666667
I0004-27	Week 8	RESPONSE	SM 6 mg	23.05555556
I0004-27	Week 8	NORESPONSE	SM 6 mg	31.66666667
I0004-27	Week 8	TOTAL	SM 6 mg	27.36111111

[PaigeMiller]

As you did in this thread, please provide the data as SAS data step code. We cannot work from screen captures.

Let's just assume that from now on, you will provide data as SAS Data step code and not as screen captures; don't make us ask for data in the proper form.

[vnreddy]

sorry for that, it's fixed

[PaigeMiller]

Thanks

Should we write code that expects more than one USUBJID and more than one TRT per USUBJID? What do we do in those cases?

If so, could you please provide an expanded example data set with more than one USUBJID and more than one TRT per USUBJID, and the desired output for that expanded example data set?

[vnreddy]

as per the data I can see only one TRT per usubjid 

updated data with one more usubjid

[PaigeMiller · Accepted solution]

PROC SUMMARY is the tool to use here

proc summary nway data=avg_score;
    class usubjid visit;
    id  trt;
    var avg_score;
    output out=_stats_(drop=_:) mean=;
run;
data want1;
    set _stats_;
    group='TOTAL';
run;
data want2;
    set avg_score _stats_(in=in2);
    by usubjid visit;
    if in2 then group='TOTAL';
run;

[vnreddy]

thank you