topic Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step in SAS Programming

How to update a SAS table using CALL EXECUTE macro function inside a Data Step

flavio_bra21 — Fri, 18 Dec 2020 00:21:28 GMT

Hello, everybody !

I'm trying to develop a code to identify a empty table and update another table " type of control table" with a information in specific field, after stop the process or depending run the process normally.

But something happen in the update step that this code doesn't work sucessfully.

Could anybody help in this task, please?

Accordin SAS code below:

data work.tasktable; * to simulating a empty source table*;

format orig_info_1 $ 1.;

format orig_info_2 $ 1.;

format dt_carga DDMMYY10.;

run;

data work.logtable; * to simulating a control table*;

format process_nm $ 7.;

format msg_log $ 30.;

format load_dt DDMMYY10.;

process_nm="ABCD123";

load_dt=today( );

put load_dt=ddmmyy10.;

RUN;

******** Assigning macro variables **********************;

options mprint;

%let ttbl=work.tasktable; %put tab_de_trabalho=&ttbl;

%let fnsh=EOF; %put end_of_file=&fnsh;

%let load_dt=%sysfunc(PUTN( %sysfunc(today()), ddmmyyS10.)); %put load_date=&load_dt;

%let load_tt=%sysfunc(PUTC(%sysfunc(today()),$10.)); %put load_date_char=&load_tt; *SAS date in char format*;

%let ltbl=work.logtable; %put log_table=&ltbl;

%let var1='XPTO567'; %put system_name=&var1;

%let var2='empty_source_file'; %put error_message=&var2;

DATA _NULL_;

FORMAT END $3.;

SET &ttbl END=&fnsh;

BY dt_carga;

IF END=&fnsh AND _N_ LE 1 AND orig_info_1=' ' THEN /* testing the condition of empty table*/

call execute ('data &ltbl;set &ltbl;format process_nm $ 7.;if &load_tt=put(today(),$10.)

and process_nm=&var1 and INDEX(msg_log," ") GE 1 then msg_log=%str(&var2);run;'); *update a character value in msg_log colunm*;

IF END=&fnsh AND _N_ LE 1 AND orig_info_1=' ' THEN

call execute ('data_null_;STOP;run;'); *stop the process*;

IF END=&fnsh AND _N_ LE 1 AND orig_info_1 NE ' ' THEN

call execute ('data_null_;output="OK";run;'); *else output=ok "do nothing" *;

RUN;

Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step

SASKiwi — Fri, 18 Dec 2020 02:00:06 GMT

You've got macro variables inside single quotes here so they won't resolve:

call execute ('data &ltbl;set &ltbl;format process_nm $ 7.;if &load_tt=put(today(),$10.)

and process_nm=&var1 and INDEX(msg_log," ") GE 1 then msg_log=%str(&var2);run;');

Try this (untested):

call execute (%nstr(%str(%')data &ltbl;set &ltbl;format process_nm $ 7.;if &load_tt=put(today(),$10.)

and process_nm=&var1 and INDEX(msg_log," ") GE 1 then msg_log=%str(&var2);run;%str(%')));

It would be better to construct the required code in a separate statement rather than doing it all in one go.

Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step

flavio_bra21 — Fri, 18 Dec 2020 02:56:38 GMT

Unfortunetelly your sugestion doesn't work:

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

25 GOPTIONS ACCESSIBLE;

27 DATA _NULL_;

28 FORMAT END $3.;

29 SET &ttbl END=&fnsh;

30 BY dt_carga;

31 IF END=&fnsh AND _N_ LE 1 AND orig_info_1=' ' THEN /* testing the condition of empty table*/

32 call execute

32 ! (%nstr(%str(%')data &ltbl;set &ltbl;format process_nm $ 7.;if &load_tt=put(today(),$10.)

WARNING: Apparent invocation of macro NSTR not resolved.

32 %nstr(%str(%')data &ltbl;set &ltbl;format process_nm $ 7.;if &load_tt=put(today(),$10.)

200

_______

252

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,

a missing value, arrayname, (, ), +, ',', -, INPUT, NOT, OF, PUT, ^, _NEW_, ~.

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR 252-185: The EXECUTE subroutine call does not have enough arguments.

Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step

SASKiwi — Fri, 18 Dec 2020 03:12:44 GMT

Sorry I got the spelling wrong: %nrstr

Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step

Tom — Fri, 18 Dec 2020 13:31:11 GMT

It does not matter if the & macro triggers are in single quotes in the data step that is pushing the code. When CALL EXECUTE pushes the code onto the stack to run the macro variable references will be resolved.

So if you run this code:

call execute('data &ltbl;set &ltbl;run;');

Then the value of LTBL macro variable will be evaluated at the time that CALL EXECUTE statement runs and code like:

data XXX; set XXX; run;

will be pushed onto the stack to run after the current data step finishes.

Are you saying the issue is that the value of LTBL is changed after the CALL EXECUTE() statement runs and you want this code to run after the data step

data &ltbl;set &ltbl;run;

so the updated value of LTBL will be used?

Re: How to update a SAS table using CALL EXECUTE macro function inside a Data Step

flavio_bra21 — Fri, 18 Dec 2020 14:25:02 GMT

Firstly, thanks for your interest in help me.
I answered that yes, the updated value of LTBL will be used. I'm trying substitute the original value of msg_log column [ ] by [&var2]. in this specific case the value doesn't change.