Problem is here. Data final_perf; *set sas.final1; /* wrong */ set final1 ; /* you have saved FINAL1 to the work library */ run; ... View more

Earlier code was copied and pasted code from one version too early: data want ; do _n_ = 1 by 1 until (last.id) ; set demo end = eof1 ; by id ; array r(2) ; array rsn_cd_ (2) $ ; array rsn_amt_ (2) ; array chkc (2) $ ("XA" "XZ") ; r(_n_) = (rsn_cd_(_n_) = chkc(_n_)) * rsn_amt_(_n_) ; maxdt = max(maxdt,svc_dt) ; end ; do _n_ = 1 by 1 until (last.id) ; set demo end = eof2 ; by id ; final_amt = 0 ; output ; if r(_n_) then do ; line = 1000 + _n_ ; svc_dt = maxdt ; final_amt = rsn_amt_(_n_) ; if rsn_cd_1 = rsn_cd_2 then final_amt = sum(of rsn_amt_(*)) ; call missing (of rsn_cd_(*)) ; do i = 1 to 2 ; rsn_amt_(i) = min(0,rsn_amt_(i)) ; end ; output ; end ; end ; drop maxdt chkc1 chkc2 r1 r2 i ; run ; proc sort ; by id line ; run ;   ... View more

If something more elegant occurs, I will post later. I am sure you will hear from some more people with nifty solutions. In the meantime try this:   data want ; do _n_ = 1 by 1 until (last.id) ; set demo end = eof1 ; by id ; array r(2) ; array rsn_cd_ (2) $ ; array rsn_amt_ (2) ; array chkc (2) $ ("XA" "XZ") ; r(_n_) = (rsn_cd_(_n_) = chkc(_n_)) * rsn_amt_(_n_) ; maxdt = max(maxdt,svc_dt) ; end ; do _n_ = 1 by 1 until (last.id) ; set demo end = eof2 ; by id ; final_amt = 0 ; output ; if r(_n_) then do ; line = 1000 + _n_ ; svc_dt = maxdt ; final_amt = rsn_amt_(_n_) ; call missing (of rsn_cd_(*)) ; do i = 1 to 2 ; rsn_amt_(i) = min(0,rsn_amt_(i)) ; end ; output ; end ; end ; drop maxdt chkc1 chkc2 r1 r2 i ; run ; proc sort ; by id line ; run ; ... View more

Can you post code to create the dataset that you HAVE? For example see how this poster has created. https://communities.sas.com/t5/SAS-Programming/Covert-date-from-character-or-numeric-before-append/m-p/648986 General instructions to create code out of a dataset are here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 ... View more

This is good but only a beginning. Can you give us similarly what is in the main pharmacy table and what you look to match? People work across a number of industries and every industry data is different. If we can't see your problem, how can we write any code to solve it? You will need to provide: 1. Code to generate Drugs dataset (you have provided) 2. Code to generate Pharmacy dataset 3. Resulting dataset ... View more

What is the variable type? Numeric that is formatted to display such or a character variable containing the above strings? Don't think you want to remove the period without also removing the zeroes that follow. Otherwise, it affects the value.   If it is a numeric variable type you can clear the format with: format <variable> ;   If it is character, you can compress the comma out. I don't think you should delete the period as it would change the value. Here is a small demo:   900        data _null_ ; 901          x = "123,679.00" ; 902          y = compress(scan(x,1,".")) ; 903          put x = y = ; 904        run ;   x=123,679.00 y=123,679   ... View more