topic Re: Macro variable resolves the same value in a do loop in SAS Programming

Macro variable resolves the same value in a do loop

jayr87 — Thu, 08 May 2014 08:47:03 GMT

Hi,

I'm trying to replicate an error I encountered.

I encountered a syntax error while running a macro.

What the macro does was to run different jobs using a do loop.

A macro variable was assign to contain all the jobs separated by a comma.

Job1 was successfull but Job2 encountered an error. Since I don't have an error handling that will make the macro code to stop after Job2 erred.

It should continue to run the other jobs(job3...jobN)

But based on the log. The remaining iterations only ran job2 until the loop and the macro was done running.

Is there a way that I can replicate my error using the code below?

%macro x;

%let cnt = 3;

%let s = job1,job2,job3;

%do i = 1 %to &cnt;

%put note: %sysfunc(scan("&s",&i,","));

data _null_;

call symputx('z',scan("&s",&i,","));

run;

%include "&z..sas";

%put &z;

%end;

%mend;

%x;

Re: Macro variable resolves the same value in a do loop

jakarman — Thu, 08 May 2014 09:01:04 GMT

If you want to submit jobs do that with a system call running a sas-script.

It is building your own scheduler as you like that.

Re: Macro variable resolves the same value in a do loop

RW9 — Thu, 08 May 2014 09:15:49 GMT

Use a dataset to store job details, then call execute using that dataset:

data jobs;

attrib jobno jobname format=$200. time_start format=datetime20.;

jobno="Job1.sas"; jobname="Job 1"; output;

jobno="Job 2.sas"; jobname="Job 2"; output;

...

run;

data _null_;

set jobs;

call execute(' %include "'||strip(jobno)||'";');

run;

Re: Macro variable resolves the same value in a do loop

jayr87 — Thu, 08 May 2014 10:10:55 GMT

Hi RW9,

This is a very helpful way to optimize my code.

But we want to know if this scenario is possible.

I have 3 jobs running using a loop statement.

Iteration1: Job1 is successful.

Iteration2: Job2 is unsuccessful.

Since we don't have an error handling. The macro continued to Iteration3.

The result is:

Iteration3: Job2 is unsuccessful.

Instead of running Job3. The 3rd iteration still ran Job2.

Please see log below:

JOB1:

SYMBOLGEN: Macro variable JOB_ITER resolves to 1

NOTE: JOBNAME = Extract_Transaction_IM

MPRINT(CALL_EXTRACT_JOBS): data _null_;

SYMBOLGEN: Macro variable PAR resolves to Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM

SYMBOLGEN: Macro variable JOB_ITER resolves to 1

MPRINT(CALL_EXTRACT_JOBS): call

symputx("etls_job",strip(scan("Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM",1,",")));

MPRINT(CALL_EXTRACT_JOBS): run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ETLS_JOB resolves to Extract_Transaction_IM

NOTE: %INCLUDE (level 1) file EXT_JOBS(Extract_Transaction_IM.sas) is file

JOB2:

MLOGIC(CALL_EXTRACT_JOBS): %DO loop index variable JOB_ITER is now 2; loop will iterate again.

MLOGIC(CALL_EXTRACT_JOBS): %PUT NOTE: JOBNAME = %sysfunc(scan("&PAR",&job_iter,","))

SYMBOLGEN: Macro variable PAR resolves to Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM

SYMBOLGEN: Macro variable JOB_ITER resolves to 2

NOTE: JOBNAME = Extract_Cash_Flow_Fact_IM

MPRINT(CALL_EXTRACT_JOBS): data _null_;

SYMBOLGEN: Macro variable PAR resolves to Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM

SYMBOLGEN: Macro variable JOB_ITER resolves to 2

MPRINT(CALL_EXTRACT_JOBS): call

symputx("etls_job",strip(scan("Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM",2,",")));

137 The SAS System 10:06 Wednesday, May 7, 2014

MPRINT(CALL_EXTRACT_JOBS): run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ETLS_JOB resolves to Extract_Cash_Flow_Fact_IM

NOTE: %INCLUDE (level 1) file EXT_JOBS(Extract_Cash_Flow_Fact_IM.sas) is file

JOB2 ERROR:

MLOGIC(CALL_EXTRACT_JOBS): %PUT ERROR: Job &gcPProd did not end successfully.

SYMBOLGEN: Macro variable GCPPROD resolves to CASH_FLOW_FACT_IM

ERROR: Job CASH_FLOW_FACT_IM did not end successfully.

JOB3:

MLOGIC(CALL_EXTRACT_JOBS): %DO loop index variable JOB_ITER is now 3; loop will iterate again.

MLOGIC(CALL_EXTRACT_JOBS): %PUT NOTE: JOBNAME = %sysfunc(scan("&PAR",&job_iter,","))

SYMBOLGEN: Macro variable PAR resolves to Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM

SYMBOLGEN: Macro variable JOB_ITER resolves to 3

NOTE: JOBNAME = Extract_Bank_IM

MPRINT(CALL_EXTRACT_JOBS): data _null_;

SYMBOLGEN: Macro variable PAR resolves to Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM

SYMBOLGEN: Macro variable JOB_ITER resolves to 3

MPRINT(CALL_EXTRACT_JOBS): call

symputx("etls_job",strip(scan("Extract_Transaction_IM,Extract_Cash_Flow_Fact_IM,Extract_Bank_IM,Extract_Other_Parties_IM",3,",")));

MPRINT(CALL_EXTRACT_JOBS): run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ETLS_JOB resolves to Extract_Cash_Flow_Fact_IM

NOTE: %INCLUDE (level 1) file EXT_JOBS(Extract_Cash_Flow_Fact_IM.sas) is file

Thanks!!!

Re: Macro variable resolves the same value in a do loop

jakarman — Thu, 08 May 2014 10:17:38 GMT

jayr as long as you execute the SAS code you are calling you cannot prevent some interactions/side effects

Detaching it by using an other batch-script this interactions effect gets broken.
Using a wait getting the returncode or nowait you can choose to defnine dependicies.

A job table having this information is a good approach. It is the basics of schedulers.

Re: Macro variable resolves the same value in a do loop

Tom — Thu, 08 May 2014 17:02:54 GMT

Since you are using a data step to generate the macro variable that you use the %INCLUDE it is most likely that the data step did not run.

Could be that the previous error had set OBS=0 and thus the data step did not work.

Or it could be that the previous code block was missing a semi-colon and so the generated DATA statement was not recognized as a new statement.

Re: Macro variable resolves the same value in a do loop

jayr87 — Fri, 09 May 2014 06:33:44 GMT

Hi Tom/Jap,

This was the error we encountered on Job2/Iteration2.

ERROR: The function ABS referenced by the %SYSFUNC or %QSYSFUNC macro function has too few arguments.

Are you saying because of this error.The data step to put Job3 in a macro variable in Iteration3 will not run, therefore Iteration3 will still run the previous value of the macro variable which is job2?

Re: Macro variable resolves the same value in a do loop

jakarman — Fri, 09 May 2014 08:01:38 GMT

Jay, The way you are doing this including sources is not predictable with results. SAS(R) 9.4 Macro Language: Reference (compiling / executing / intepreter).

the code you are including can contain everything that possible is disturbing your macro execution.

Suppose someone in that included code did: " %let i=1 ; " By that your macro will always continue with i=2 job=2 no matter what you do.

The approach of RW9 using call execute is avoiding that.

See also http://support.sas.com/resources/papers/proceedings13/032-2013.pdf as a new way could be dosubl, but having the same feedback. SAS(R) 9.4 Functions and CALL Routines: Reference, Second Edition (dosubl)

Other possible unwanted influences you are having are:

- all work/macro/options settings done in prog1 will be propagated to the next ones.

- every error condition in a called program will influence your macro.

That is why I adviced to used mp-connect or OS sas scritpting. It will break those influences as all get their own initialization.

Re: Macro variable resolves the same value in a do loop

jwillis — Fri, 09 May 2014 12:35:38 GMT

jayr87,

Did the "gc" macro variable resolve correctly in Job2? Are your macro variables 'global' where they need to be?

MLOGIC(CALL_EXTRACT_JOBS): %PUT ERROR: Job &gcPProd did not end successfully.

Re: Macro variable resolves the same value in a do loop

jayr87 — Mon, 12 May 2014 07:58:17 GMT

Hi jwillis,

Yes the &gcpprod was resolve. Please see below.

But this macro is not globally declared through out the job. It is only declared in CASH_FLOW_FACT_IM.sas

SYMBOLGEN: Macro variable GCPPROD resolves to CASH_FLOW_FACT_IM

ERROR: Job CASH_FLOW_FACT_IM did not end successfully.

Regards,

Jayr

Re: Macro variable resolves the same value in a do loop

jakarman — Mon, 12 May 2014 09:00:55 GMT

jayr,

I went carefully through your macro and logging. guess what, works as coded.

- You macro is compiled nor errors/warnings. starting to run

- The macro iteration is started and will run 3 times...

- within the iteration a data-step is used to define a macrovar.

After the macrovar has been defined the value is being used in a %include.

Iteration 1. .... all well

Iteration 2 we are seeing an Error.

Normal SAS behavior is going into "syntax check mode". We are missing this message and all of the include coded processing probably by a "proc printto" or other redirect.

Datasteps proc will give notes zero resource usage, macro processing still going on.

Iteration 3. .... The datastep will not run for updating your macro-var

The include will show the value of step2 ...... expecting nothing to be done there

This is all documented behavior. Why would you want it to do different?

Re: Macro variable resolves the same value in a do loop

Astounding — Mon, 12 May 2014 12:51:56 GMT

jayr87,

Just a couple of notes ... This statement is suspect:

%put note: %sysfunc(scan("&s",&i,","));

First, note that there is a %SCAN function, so you don't need to use the combination of %SYSFUNC and SCAN. Second, macro language assumes you are working with character strings for most arguments. You don't need double quotes as you would in a DATA step to indicate that an argument is character. In this example, therefore, you are requesting that both double quotes and commas be treated as delimiters. The combination that macro language would normally use (whether or not you eliminate %SYSFUNC) would be:

%put note: %scan(%str(&s),&i,%str(,));

The %STR function treats commas as text, rather than as significant characters.

If the final DATA or PROC step generated an error, you can skip remaining jobs pretty easily. Replace this statement:

%include "&z.sas";

Instead, use:

%if &syserr=0 %then %do;

%include "&z.sas";

%end;

Note that &SYSERR resets for every DATA and PROC step, so it does not check whether the previous job contains any errors at all. It only checks whether the last DATA or PROC step ran successfully.

Hope this is useful.

Re: Macro variable resolves the same value in a do loop

jayr87 — Tue, 13 May 2014 01:53:37 GMT

Hi Jaap,

Thank you for this.

Our objective is for the 3rd job to run even though the 2nd job erred.

How can we do this?

Thanks!

Jayr

Re: Macro variable resolves the same value in a do loop

jayr87 — Tue, 13 May 2014 01:55:57 GMT

Hi Astounding,

Will the marcovar &z resolved to job3?

%if &syserr=0 %then %do;

%include "&z.sas";

%end;

Thanks!

Jayr

Re: Macro variable resolves the same value in a do loop

jakarman — Tue, 13 May 2014 05:40:08 GMT

As your SAS session is needing to be segregated form the running sources possible ruining your session or each other, there are several solutions.

It is possible to run asynchronously (all jobs starting same moment) or serial (synchronously, waitfor). Serial processing will take longer but is easier to understand.

1- one of the many X-cmd usages. statement, Piping (serial), datastep/macro

systask is having more options: SAS(R) 9.3 Companion for UNIX Environments

pipe is forcing you to wait and you, advantage you can see more of the OS-messages. SAS(R) 9.3 Companion for UNIX Environments

2- usage of mp-connect

SAS/CONNECT(R) 9.3 User's Guide (Example 5: Using MP CONNECT and the WAITFOR Statement)

Watch the !sascmd (the exclamation is used for the OS value ot that variable) you could use the same using a x-cmd

Re: Macro variable resolves the same value in a do loop

Astounding — Tue, 13 May 2014 12:42:50 GMT

Yes, although you may need to insert an extra dot:

%include "&z..sas";

The first denotes the end of the name of the macro variable, and the second becomes the dot between "job3" and "sas".