OK then just catch this case data ds1; input aid trndt mmddyy6. amount; format trndt ddmmyy6.; datalines; 1 102416 100 1 102216 600 1 102316 300 1 101016 500 2 102416 100 2 102616 600 2 102316 300 2 101016 500 run; proc sort data=ds1; by aid descending amount; run; data want; length lag1_amount 8; set ds1; by aid descending amount; retain counter; lag1_amount=lag1(amount); if first.aid then counter=1; else if lag1_amount ne amount then counter+1; if (today()-7)<=trndt<=today() and counter eq 2 then want=1; run; ... View more

Hi, The amount for the condition "where trndt between today() and (today()-7)" is never the second highest. I wouldn't use SQL for this. data ds1; input aid trndt mmddyy6. amount; format trndt ddmmyy6.; datalines; 1 102416 100 1 102216 600 1 102316 300 1 101016 500 2 102416 100 2 102616 600 2 102316 300 2 101016 500 run; proc sort data=ds1; by aid descending amount; run; data want; set ds1; by aid descending amount; retain counter; if first.aid then counter=0; counter+1; if (today()-7)<=trndt<=today() and counter eq 2 then want=1; run; Cheers, Oligolas ... View more

%MACRO sum(k1,k2); PROC SQL; UPDATE calculated2 SET sum_num=(select sum(num) FROM calculated WHERE k1 eq &k1. AND k2 eq &k2. GROUP BY k1,k2) WHERE k1 eq &k1. AND k2 eq &k2. ; QUIT; %MEND; DATA calculated2; length new_field sum_num 8; drop rc; if _n_=1 then do; call missing(new_field); declare hash NF(); NF.definekey('k1'); NF.definedata('new_field'); NF.definedone(); NF.add(key:1, data:.); NF.add(key:2, data:1); end; set input; call execute('%'||'sum('||put(k1,best.)||','||put(k2,best.)||');'); rc=NF.find(); RUN; Nice problem, thanks for asking 🙂 Cheers, Oligolas ... View more

My understanding is, that since the output works in a different way with the MODIFY statement, we can not expect to get the same results as with the SET statement.   The notifications are not appearing precisely because of the output statement in this context. The mnemonic operators are fine, it is output in this context that makes troubles.   The SAS help says: If a MODIFY statement is present, OUTPUT with no arguments writes the current observation to the end of the data set that is specified in the MODIFY statement. When Using the MODIFY Statement: When you use the MODIFY statement with the OUTPUT statement, the REMOVE and REPLACE statements override the implicit write action at the end of each DATA step iteration. See Comparisons for more information. If both the OUTPUT statement and a REPLACE or REMOVE statement execute on a given observation, perform the output action last to keep the position of the observation pointer correct. Looks like we can not get it all done in one run. You are trying to do a kind of full outer join and I doubt if this is possible with MODIFY (maybe contact the SAS support to get their advice) For my part I prefer doing a full outer join in a first run and program the notifications in a second run. PROC SQL; CREATE TABLE example AS SELECT coalesce(t1.a, t2.a) AS a, t1.a AS a_master1, t2.a AS a_transaction, coalesce(t1.b, t2.b) AS b FROM master1 t1 FULL OUTER JOIN TRANSACTION t2 ON t1.a eq t2.a ; QUIT; data _NULL_; set example; if missing(a_master1) then put '...'; ... run;    Cheers, Oligolas   ... View more

Hi expertyejin   the output statement behaves differently if used in combination with the modify statement.   "If a MODIFY statement is present, OUTPUT with no arguments writes the current observation to the end of the data set that is specified in the MODIFY statement."   So a=1 is going to be checked, it outputs the row at the end of master1, same for a=3 then it goes through transaction for a match replace: - for a=1 it replaces b as long as matches are found, first with 8, then with 7 and finally with 6 - same procedure for a=3, first 4, then 3 and finally 2   I Hope everything was understandable so far. Cheers, Oligolas ... View more