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..
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: