topic Re: Retain Previous Non Empty Value by Group in SAS Programming

Retain Previous Non Empty Value by Group

hellind — Fri, 04 Mar 2022 13:37:36 GMT

An Account once enrolled in a program continues to be enrolled until it gets enrolled in another program

HAVE:

Account Number	Date	Program ID
23452397	Jan 2021
23452397	Feb 2021	337
23452397	Mar 2021
98668953	Jan 2021
98668953	Feb 2021	10180
98668953	Mar 2021	338
98668953	Apr 2021

WANT:

Account Number	Date	Program ID
23452397	Jan 2021
23452397	Feb 2021	337
23452397	Mar 2021	337
98668953	Jan 2021
98668953	Feb 2021	10180
98668953	Mar 2021	338
98668953	Apr 2021	338

Assume the data is already sorted.

Re: Retain Previous Non Empty Value by Group

Astounding — Fri, 04 Mar 2022 13:45:19 GMT

The easy way will apply to all variables, not just Program_ID:

data want;
   update have (obs=0) have;
   by Account_Number;
   output;
run;

Re: Retain Previous Non Empty Value by Group

mkeintz — Sat, 05 Mar 2022 05:40:28 GMT

But you can apply it to just selective variables. Say you want to apply it to program_id only, but you have another variable (EXTRA) that you do NOT want to carry forward to overwrite missing values:

data want;
  update have (obs=0 ) have (keep=account_number program_id);
  by account_number;
  set have (keep=extra);
  output;
run;

In this case, I assume that DATE is never missing, so no special treatment is needed for it.

Re: Retain Previous Non Empty Value by Group

Kurt_Bremser — Sat, 05 Mar 2022 06:01:39 GMT

To do it with just SET, see this:

data want;
set have (rename=(program_id=_p)),
by account_number;
retain program_id;
if first.account_number then program_id = "".
program_id = ifc(_p,_p,program_id);
run;

Untested, posted from my tablet.

Adapt the code if program_id is numeric..