@SASdn wrote: Datalines step is not the actual one.. it's just indicative. You can ignore the transaction truncation.   Test dataset is not sorted before that.. and needs to be used in a sorted form within data step. As I mentioned earlier, I have a call execute statement after by with area_code as a parameter. So the macros need to be executed in the alphabetical order for certain functionality. I guess removing by would execute it random order. Show the order the example data needs to be processed. Likely the issue is an unneeded "by statement" in the data _null_.   Removing the BY statement will run them in the order of appeareance in the data set. If you need to to so something based on FIRST. or LAST. processing then NOTSORTED will not change the order or make it a random order it just makes the first and last operations available.   If you need to feed data into a data step in a specific order and your original data is not in that order then you will need to provide some rule for getting things into that order. But we can't help with that if we don't know what the order may be. Possibly you create a data set with the value of your "area_code" or whatever variable and an order value. Then combine that set with your raw data to get the order variable attached to each record. Then sort by the order variable. If you use Proc Sql you can do the combine and sort in the same step. I don't know if the substitute tests set I create below is valid for your project but demonstrates the reorder to group by the specified order data work.tests; input area_code $ flag $; datalines; test 1 transaction 1 txnfdh 1 txn_iadbo 1 transaction 3 txnfdh 2 ; run; data work.order; input area_code $ order; datalines; test 1 transaction 2 txnfdh 3 txn_iadbo 4 ; run; proc sql; create table work.ordered as select b.order, a.* from work.tests as a left join work.order as b on a.area_code=b.area_code order by b.order; quit; ... View more

Since you cannot rollback an email, it makes more sense to me to do the update first and rollback if the message is not sent.   proc sql noprint; Connect to Greenplm as dbcon (user=&user pw=&pass server=&HPAHost port=5432 database=aae_data); SELECT * INTO :email_flg FROM connection to dbcon ( SELECT count(1)::smallint FROM f2_t1 WHERE rflag = 1 AND (process_flag <> 'E') ); disconnect from dbcon; quit; %if &email_flg > 0 %then %do; * try to update first; proc sql; Connect to xxxx as dbcon (user=xxx pw=xxx server=xxxx port=xxxx database=aae_data); execute ( update f2_t1 set process_flag = 'E' where run_flag = 1) by dbcon ; disconnect from dbcon; quit; * updated?; %if not &SQLXRC %then %do; %let email_body = Hello world; %let to = aa@ss.com; %let cc = ab@ss.com %let subject_text = Test; filename outbox email &to; data _null_; file outbox to=(&to) cc=(&cc) subject=&subject_text ; put &email_body; run; * email sent?; %if &SYSERR %then %do; * rollback the update; proc sql; Connect to xxxx as dbcon (user=xxx pw=xxx server=xxxx port=xxxx database=aae_data); execute ( <ROLLBACK HERE> ) by dbcon ; disconnect from dbcon; quit; %end; %end; %end;   I have not tested the code, and you must check how to issue a rollback on your greenplum platform.   Hope it helps.   Daniel Santos @ www.cgd.pt ... View more

I cannot test it, but mainly I believe it is posiible. I sugget to try it on a sample in work library;   FILENAME OUTBOX ...; data <dsname>;   modify <dsname>;      if flag = 0 then do;         flag = 1;         file outbox ...;               to = ...;               cc = ....;               subject = ....;               put ....;           end;      if _iorc_ = 0 then replace; run; ... View more

Besides what the others said, your data _null_ is completely useless. The %let macro statements create no code for the data step, so it stays empty. ... View more

I suggest you explore the options available to you with scheduling tools. For example you could break your 7 - 8 hour job down into say 3 or 4 processes, then use the scheduler to only start process 2 if you get a zero return code with process 1 and so on. That way if there is a failure, your job will stop as early as possible. Then you use your scheduler to start after the last good step.   Hint: make sure your SAS batch jobs have the SYNTAXCHECK option switched on. With this option SAS sets OBS = 0 at the first error and very quckly finishes a job as it processes no data after the error.  ... View more