Retain with array

[jk2018]

I have multiple observations for people and want to end up with one observation per person.  I also want to retain any YES responses and drop any NO responses from any of the observations for about 20 variables with very different names.  

[Reeza]

Use SQL and take the max of each column.

proc sql;
create table want as
select ID, max(col1) as col1, max(col2) as col2 ....
from have
group by ID;
quit;

Problem with this approach - you have to list each variable out.

[mkeintz]

I don't follow what your input, or desired output looks like.  Could you please provide the input data in the form of a working data step?  That would facilitate generating test code.    And then please show what the desired output should look like.

[jk2018]

The dataset I'm starting with is like this:

OBS	NAME	DOB	TOOK_MEDICATION	HIGH_BP	LOW_BP
1	DOE, JOHN	01/01/1950	NO	NO	NO
2	DOE, JOHN	01/01/1950	NO	NO	YES
3	DOE, JOHN	01/01/1950	NO	YES	NO
4	DOE, JOHN	01/01/1950	YES	YES	NO
5	DOE, JANE	12/31/1955	NO	NO	NO
6	DOE, JANE	12/31/1955		YES	NO

I want to end up with this:

OBS	NAME	DOB	TOOK_MEDICATION	HIGH_BP	LOW_BP
1	DOE, JOHN	01/01/1950	YES	YES	YES
2	DOE, JANE	12/31/1955		YES

[mkeintz · Accepted solution]

The sample data is not in the form of a working data step, so here is untested code, but at least I know the general layout now:

data want;
  set have;
  by name notsorted;
  array vars took_medication -- low_bp;
  array tmp {20} _temporary_;
  if first.name then call missing(of tmp{*});
  do over vars;
    if vars='YES' then tmp{_i_}=1;
  end;
  if last.name;
  do over vars;
    if tmp{_i_}=1 then vars='YES';
    else vars=' ';
  end;
run;

Make sure the size of the TMP array is at least as many as there are variables to be examined.  TMP can exceed that number with no ill effects.

[andreas_lds]

That problem would be really easy to solve if the ccc-vars would have 1 and 0 instead of yes/no as values.

proc format;
   value YesNo
      1 = 'Yes'
      0 = 'No'     
   ;
run;

proc summary data=work.have nway;
   class Name;
   var took_medication -- low_bp;
   format took_medication -- low_bp YesNo.;
   output out= work.want max=;
run;