topic Re: Replacing semi-duplicated rows with average column values in SAS Programming

Replacing semi-duplicated rows with average column values

farshidowrang — Fri, 24 Apr 2020 16:59:22 GMT

Dear friends,

How can I average column values of semi-duplicated rows and replace them with only one row?

In the following table (HAVE) the rows A5, A10, B7 and C6 are duplicated.

time	anomaly
A1	42
A2	43
A3	45
A4	48
A5	55
A5	51
A6	65
A7	75
A8	63
A9	50
A10	48
A10	47
A10	51
A10	55
A11	42
A12	44
B1	125
B2	128
B3	125
B4	132
B5	139
B6	141
B7	158
B7	159
B7	161
B7	147
B7	144
B8	150
B9	142
B10	147
B11	122
B12	123
C1	1135
C2	1139
C3	1135
C4	1144
C5	1147
C6	1151
C6	1144
C7	1159
C8	1144
C9	1140
C10	1138
C11	1133
C12	1129

I need only one row of each of those duplicated rows like below:

time	anomaly
A1	42
A2	43
A3	45
A4	48
A5	53
A6	65
A7	75
A8	63
A9	50
A10	50.25
A11	42
A12	44
B1	125
B2	128
B3	125
B4	132
B5	139
B6	141
B7	153.8
B8	150
B9	142
B10	147
B11	122
B12	123
C1	1135
C2	1139
C3	1135
C4	1144
C5	1147
C6	1147.5
C7	1159
C8	1144
C9	1140
C10	1138
C11	1133
C12	1129

Re: Replacing semi-duplicated rows with average column values

PaigeMiller — Fri, 24 Apr 2020 17:02:30 GMT

proc summary data=have nway;
    class time;
    var anomaly;
    output out=want mean=;
run;

Please provide data as a SAS data step. Do not provide data as screen captures. We have asked for your compliance previously, you would help us, and help yourself, by not providing data as screen captures.

Re: Replacing semi-duplicated rows with average column values

farshidowrang — Fri, 24 Apr 2020 18:02:54 GMT

Dear Paige,

I like to have it in a table and not as a summary,

Best regards
Farshid

Re: Replacing semi-duplicated rows with average column values

PaigeMiller — Fri, 24 Apr 2020 18:09:09 GMT

You can use PROC PRINT

Re: Replacing semi-duplicated rows with average column values

Daryl — Sat, 25 Apr 2020 04:02:02 GMT

In PROC SQL:

data have;
infile datalines;
input time $ anomaly;
datalines;
A1	42
A2	43
A3	45
A4	48
A5	55
A5	51
A6	65
A7	75
A8	63
A9	50
A10	48
A10	47
A10	51
A10	55
A11	42
A12	44
B1	125
B2	128
B3	125
B4	132
B5	139
B6	141
B7	158
B7	159
B7	161
B7	147
B7	144
B8	150
B9	142
B10	147
B11	122
B12	123
C1	1135
C2	1139
C3	1135
C4	1144
C5	1147
C6	1151
C6	1144
C7	1159
C8	1144
C9	1140
C10	1138
C11	1133
C12	1129
;
run;
proc sql;
 create table need (drop=ob) as
 select distinct(time),mean(anomaly) as anomaly, monotonic() as ob from have
 group by time
 order by ob;
 quit;
 run;