Hello @danielchoi626 This can be done as shown below. Modify as needed. I have used your excel as the basis. In your excel had amount as character. So I have added code to convert it to number.
/*Import your excel sheet */
PROC IMPORT DATAFILE='/home/yourfolder/SAMPLE DATA.xlsx'
DBMS=XLSX
OUT=WORK.IMPORT replace;
GETNAMES=YES;
RUN;
/* Your amount was character.
I am converting the amount to number. If in your original dataset amount is numeric, then this step not needed */
data import;
set import (Rename=(Amount=Amount_));
Amount=input(Amount_,best12.);
drop amount_;
run;
/* Remove duplicates*/
proc sql;
select distinct a.id_no, b.appl_no,b.Amount from import as a
left join import as b
on a.ID_no=b.ID_no
where b.amount is not missing
order by ID_no;
quit;
The output will be as you wanted. The not null values from amount have been retained with no duplicates
Let me know if there are questions.
... View more