topic Re: Issue in update statement in data step in SAS Studio

Issue in update statement in data step

ALOKVB86 — Wed, 14 Nov 2018 11:16:15 GMT

Update statement in data step is not executing through SAS DI but same is executing through EG. PFB the code we are trying to execute.

DATA TRANSACTION;
FORMAT JOB_RUN_SKEY 20. JOB_END_TIME DATETIME20.;
JOB_STATUS_CDE = -1;
JOB_RUN_SKEY =&JOB_RUN_SKEY.;
JOB_END_TIME=DATETIME();
RUN;

data ODSDMSAS.DSI_PROD_CTRL_TBL;
modify ODSDMSAS.DSI_PROD_CTRL_TBL (WHERE=(JOB_RUN_SKEY =&JOB_RUN_SKEY.))
transaction
;

by JOB_RUN_SKEY;
RUN;

Log:

NOTE: The data set ODSDMSAS.DSI_PROD_CTRL_TBL has been updated.  There were 0 observations rewritten, 0 observations added and 0 observations deleted.
NOTE: There were 0 observations read from the data set WORK.TRANSACTION.

Re: Issue in update statement in data step

RW9 — Wed, 14 Nov 2018 11:33:28 GMT

Please avoid shouting uppercase code at us. The first thing that jumps out at me is that you are filtering the master dataset (with a where clause), but this is not how update works. If you are trying to add a new record, then set the small dataset to the big one. If you updating where the id exists, then the by line will do that. Also, as you have not provided any test data - in the form of a datastep, nor even provided what the macro job_key_skey is, we have nothing to work with to diagnose any further.

Re: Issue in update statement in data step

ballardw — Wed, 14 Nov 2018 15:51:35 GMT

This statement

NOTE: There were 0 observations read from the data set WORK.TRANSACTION.

says there were no records in the data set you named transaction. So with nothing read from that set nothing was available to update or modify the data. So you need to examine the log for where your transaction set is made. Since we have nothing for your macro variable &Job_run_skey we cannot tell whether that might be an issue or not. If the macro variable was undefined then the data step would have and error and an empty data set created.

Modify is no the same as Update though.

Re: Issue in update statement in data step

ALOKVB86 — Fri, 16 Nov 2018 11:23:11 GMT

I am intending to update status table once DI job gets completed. Once job executes successfully status_code updates properly to 1 but once encountered an error it's not updating at all. We tried proc sql but it was not working so tried data step to update.

MPRINT(JOB_IS_ERRR_EXIST): DATA TRANSACTION;
MPRINT(JOB_IS_ERRR_EXIST): FORMAT JOB_RUN_SKEY 20. JOB_END_TIME DATETIME20.;
MPRINT(JOB_IS_ERRR_EXIST): JOB_STATUS_CDE = -1;
SYMBOLGEN: Macro variable JOB_RUN_SKEY resolves to 1857990024574
MPRINT(JOB_IS_ERRR_EXIST): JOB_RUN_SKEY =1857990024574;
MPRINT(JOB_IS_ERRR_EXIST): JOB_END_TIME=DATETIME();
MPRINT(JOB_IS_ERRR_EXIST): RUN;

MPRINT(JOB_IS_ERRR_EXIST): data ODSDMSAS.DSI_PROD_CTRL_TBL;
MPRINT(JOB_IS_ERRR_EXIST): modify ODSDMSAS.DSI_PROD_CTRL_TBL TRANSACTION ;
MPRINT(JOB_IS_ERRR_EXIST): by JOB_RUN_SKEY;
MPRINT(JOB_IS_ERRR_EXIST): RUN;
36 The SAS System 12:20 Friday, November 16, 2018

JOB_RUN_SKEY	JOB_ID	JOB_NAME	JOB_SAS_CDE	TGT_TBL_NME	TGT_TBL_DESC	JOB_ETL_NME	JOB_START_TIME	JOB_END_TIME	JOB_STATUS_CDE
1857990024574	574	ABR_PREPAID_CARDS_JOB				ABR_PREPAID_CARDS_JOB	16Nov2018 12:20:24		0

Status Code 0 meaning job has been executed. Once job got an error status code didn't change.

Please help