topic Retain with array in SAS Programming

Retain with array

jk2018 — Tue, 06 Jul 2021 19:02:09 GMT

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.

Re: Retain with array

Reeza — Tue, 06 Jul 2021 19:18:49 GMT

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.

Re: Retain with array

mkeintz — Tue, 06 Jul 2021 20:40:30 GMT

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.

Re: Retain with array

jk2018 — Tue, 06 Jul 2021 21:13:36 GMT

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

Re: Retain with array

mkeintz — Tue, 06 Jul 2021 22:06:07 GMT

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.

Re: Retain with array

andreas_lds — Wed, 07 Jul 2021 05:08:24 GMT

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;