topic Re: Combining multiple rows to a single row in SAS Programming

Combining multiple rows to a single row

Bright — Tue, 17 May 2022 14:20:15 GMT

Hi,

I have a dataset as below:

ID	Condition A	Condition B	Condition C	Condition D
250	0	0	0	0
250	0	1	0	0
250	1	0	0	0
260	0	0	0	0
260	0	0	0	0
270	1	0	0	0
270	0	1	0	0
270	0	0	0	1

I want an output like the table below:

ID	Condition A	Condition B	Condition C	Condition D
250	1	1	0	0
260	0	0	0	0
270	1	1	0	1

Thanks!

Re: Combining multiple rows to a single row

japelin — Tue, 17 May 2022 14:25:58 GMT

If values are only 0 or 1, how about this.

data have;
  input id a b c d;
datalines;
250	0	0	0	0
250	0	1	0	0
250	1	0	0	0
260	0	0	0	0
260	0	0	0	0
270	1	0	0	0
270	0	1	0	0
270	0	0	0	1
;
run;

proc sql;
  create table want as
  select id,
         max(a) as a,
         max(b) as b,
         max(c) as c,
         max(d) as d
         from have
  group by id
  ;
quit;

Re: Combining multiple rows to a single row

Kurt_Bremser — Tue, 17 May 2022 14:26:55 GMT

proc summary data=have nway;
class id;
var cond:;
output
  out=want (drop=_type_ _freq_)
  sum()=
;
run;

Untested; for tested code, provide data in a working data step with datalines.

(working = no WARNINGs, no ERRORs)

Re: Combining multiple rows to a single row

Tom — Tue, 17 May 2022 14:33:25 GMT

Can we assume that variable are boolean 1/0 numeric values ?

Then just use PROC SUMMARY.

If the goal is to get an aggregate boolean values to indicate if the condition ever occurred then use the MAX statistic.

If the goal is to get a count of how many times the condition occurred then use the SUM statistic.

proc summary data=have nway;
  class id ;
  var condition:;
  output out=want max= ;
run;

If the data is already sorted by ID then use BY ID instead of CLASS ID.