Like this?
data table1;
infile cards dsd dlm=",";
input
ID $
EDATE :mmddyy10.
RATING $
OUTLOOK :$50.
CREDITWATCH :$50.
;
format
EDATE mmddyy10.
;
cards;
1,02/20/2020,,positive outlook,
1,02/20/2020,B-,,
1,04/08/2020,,,developing watch
1,04/09/2020,CCC+,,
2,03/28/2018,,negative outlook,
2,03/28/2018,B,,
2,02/24/2020,,,negative watch
2,02/24/2020,,stable outlook,
2,02/24/2020,B-,,
run;
data WANT;
merge TABLE1 ;
by ID EDATE;
length KEEP_RATING KEEP_OUTLOOK KEEP_CRWATCH $16;
retain KEEP_RATING KEEP_OUTLOOK KEEP_CRWATCH;
if first.ID then call missing(KEEP_RATING);
if first.EDATE then call missing(KEEP_OUTLOOK, KEEP_CRWATCH);
KEEP_RATING =coalescec(RATING ,KEEP_RATING );
KEEP_OUTLOOK=coalescec(OUTLOOK ,KEEP_OUTLOOK);
KEEP_CRWATCH=coalescec(CREDITWATCH,KEEP_CRWATCH);
if last.EDATE then do;
RATING = KEEP_RATING;
OUTLOOKORWATCH = ifc( KEEP_OUTLOOK^=' ' and KEEP_CRWATCH^=' ', 'wrong'
, ifc( KEEP_OUTLOOK =' ' and KEEP_CRWATCH =' ', 'stable outlook'
, coalescec(KEEP_OUTLOOK ,KEEP_CRWATCH)));
output;
end;
keep ID EDATE RATING OUTLOOKORWATCH;
run;
proc print; run;
Obs
ID
edate
Rating
OUTLOOKORWATCH
1
1
02/20/2020
B-
positive outlook
2
1
04/08/2020
B-
developing watch
3
1
04/09/2020
CCC+
stable outlook
4
2
03/28/2018
B
negative outlook
5
2
02/24/2020
B-
wrong
Note that there are tabs in your code. They mess up the data. Do not do that.
... View more
