topic Wanting to condense multiple observations per ID to single in SAS Programming

Wanting to condense multiple observations per ID to single

timmy94 — Sun, 14 Jul 2024 03:30:54 GMT

Hello!

I have a dataset with multiple entries per ID, and would like to condense to a single entry per ID, which shows is any of the entries were positive for a given variable.

What I have:

ID A B

1 0 0

1 1 0

1 0 0

2 1 1

2 0 0

3 0 0

What I want:

ID Any_A Any_B

1 1 0

2 1 1

3 0 0

Many thanks

Re: Wanting to condense multiple observations per ID to single

Tom — Sun, 14 Jul 2024 03:58:53 GMT

To find if ANY are TRUE then take the MAX().

You can use PROC SUMMARY.

proc summary data=have;
  by id;
  var a b ;
  output out=want max= ;
run;

Re: Wanting to condense multiple observations per ID to single

ballardw — Sun, 14 Jul 2024 05:22:45 GMT

I think you may be looking for:

proc summary data=have nway;
   class Id;
   var A B;
   output out=want (drop=_type_ _freq_) max=Any_A Any_B;
run;

The NWAY option requests only the greatest value of the _type_ variable which indicates the specific variables on the CLASS statement are used for specific output. The default behavior for Proc Summary is to provide a summary overall of all values plus combinations of variables appearing on the Class statement.

The Drop removes variables not requested that would indicate the combination and count of per combination in the _freq_. the MAX statistic will keep the 1 if present and the = indicates the names of the output variable(s) for the statistics requested.