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.
The easy way will apply to all variables, not just Program_ID:
data want;
update have (obs=0) have;
by Account_Number;
output;
run;
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.
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..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.