topic Re: Running macro multiple times in SAS Programming

Running macro multiple times

kajal_30 — Wed, 18 May 2022 14:44:54 GMT

I need to run macro call multiple times :

data have;

do id = dt ( for dt I have values like 201407,201508,201605,201706,201804,201905 got from another macro)

call execute(cats(%nrstr(%creating_data_tables(out_tbl = abc , in_file = bnk_mkt_scr_dt.xlsx) ;

end;

run;

I want to call this macro for different values of yearmonth dates available in dt . I tried the above but didn't work

Thanks

Kajal

Re: Running macro multiple times

acordes — Wed, 18 May 2022 14:51:46 GMT

Try adapting this code which runs successfully for me in order to rename variables over different tables.

%let filesas=_201112servilease;
libname curr "%trim(&path2whole2)";
 
data _null_;
set dirxls;
call execute('%renamehelp (whole2._'||strip(file_name)||', f_fin   , F_END); ');
run;
 
 
%macro renamehelp (sasf, oldN, newN);
 
data &sasf;
set &sasf (rename=(&oldN=&newN));
run;
 
%mend;

Re: Running macro multiple times

PaigeMiller — Wed, 18 May 2022 15:00:34 GMT

A macro isn't needed here (maybe a macro variable is needed). A data step DO loop should work.

But, the important advice if you're going to write a macro is to produce working SAS code without macros and macro variables for this problem with just one or two dates. If you don't have working code without macros and macro variables, then it will never work with macros or with macro variables. Most people ignore this advice, and their macro code never works. Don't ignore this advice. Show us working code without macros and without macro variables.

Re: Running macro multiple times

Tom — Wed, 18 May 2022 14:59:18 GMT

Where do you have the list of values of DT?

Is DT intended to be a character string like: '201407'

Are number like: 201,407

Or a date value like, 01JUL2014, that has been formated with the YYMMN6. format?

Or some other strange date like 14JUL2020?

Do you want to generate the filename from the value of ID (or DT)?

So something like this will generate a dataset with one observation per ID value and also generate one call to the macro for each ID.

data want;
  length id 8 filename $100;
  do id = 201407,201508,201605,201706,201804,201905;
    filename=cats('bnk_mkt_scr_',id,'.xlsx');
    call cxecute(catx(' '
         ,'%nrstr(%creating_data_tables)(out_tbl=abc,in_file='
         ,filename
         ,')'
     ));
     output;
  end;
run;

If instead you already have the list of ID (or DT) values in a dataset then just use a data _null_ step instead.

data _null_;
  set have;
  filename $100;
  filename=cats('bnk_mkt_scr_',id,'.xlsx');
  call cxecute(catx(' '
       ,'%nrstr(%creating_data_tables)(out_tbl=abc,in_file='
       ,filename
       ,')'
   ));
run;

Re: Running macro multiple times

ballardw — Wed, 18 May 2022 15:14:34 GMT

Didn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "<>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "<>" icon or attached as text to show exactly what you have and that we can test code against.

Quite often when using macros the first debug option is to set OPTIONS MPRINT before code that executes macro to se what the macro generates. The error messages, if any, will also then appear in closer proximity to code generating them.

Just to confirm you did compile the macro in the current session before running the code, didn't you?

Your code is sending a litteral dt to the macro where you highlight it. There is nothing that supplies values to the variable dt (on the right of the = sign) as you have no SET or similar statement to reference a data set to provide them. You are not using a macro variable as shown. You would have to show the actual value of the macro variable to have a chance of this working.

If your

id = dt ;/* note you did not have a ; to end the statement*/

was supposed to be

id = &dt;

why does that statement exist? If your &dt macro variable contains multiple values that you intend to use one at a time then you need to get stuff from the macro and split it up, not copy it.

Maybe something along these lines (depends a LOT on actual value of the not shown macro variable dt.

data Junk;
 length longstr $ 200;
   Do i= 1 to countw("&dt.");
      dttemp = scan("&dt.",i);
      longstr = cats('%creating_data_tables(out_tbl = abc , in_file = bnk_mkt_scr_',dttemp,'.xlsx);');
      output;
      call execute(longstr);
   end;
run;

Suggestion would be to run the above with the CALL Execute commented out so that it does not execute and examine the values of the LONGSTR variable to see if the code for the macro call is correct. If so, then uncomment the call execute. The test without call execute takes so little time you lose not much time compared to trying to figure out why all the executes you create are throwing errors.

HINT: Your code as designed is very useless as the apparent output is reused and the result will only be the version for the last value of the dt variable. This is based on a macro parameter named OUT_TBL which I expect to be the desired result. You overwrite it with each execution of the macro.

Re: Running macro multiple times

kajal_30 — Wed, 18 May 2022 16:37:35 GMT

Here is my full macro definition :

libname output '/sas/out/data';

%macro creating_tables (out_table =
			,in_file  = 
			);
	PROC IMPORT 
		OUT= &out_table
		DATAFILE= "sas/repo/files/&in_file..xlsx" 
		DBMS=xlsx 
		REPLACE;
		GETNAMES=YES;
	RUN;

	DATA  &out_table;
		SET  &out_table;
		LOAD_TIME = datetime();
		yearmonth = "&yearmonth";
		FORMAT LOAD_TIME datetime20.;
	RUN;

	proc append base = output.&out_table
		data =   &out_table;
	run;

%mend creating_tables;
%creating_tables(out_table = Canada_bkn_score 
,in_file = qtr_src_file_&yearmonth..xlsx
);

and I want to call this macro multiple times for different values of yearmonth. So I can have those values either stored in a macro variable or as a column value in a dataset.

Re: Running macro multiple times

Quentin — Wed, 18 May 2022 16:55:43 GMT

Please see Tom's proposed solutions.

You basically need either a dataset of file names, or you can use a DO loop to generate the date part of the file names.

Be sure to use CALL EXECUTE like Tom showed, with %nrstr, and also single quote marks. Your original post is missing single quote marks around the string passed as an argument to CALL EXECUTE.

Re: Running macro multiple times

kajal_30 — Wed, 18 May 2022 17:23:37 GMT

I am getting below error after running the suggested code. But this macro call is running fine if I am running it out of data step i.e. not using call execute for one yearmonth.

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

The SAS task name is [IMPORT (]
Segmentation Violation

Traceback of the Exception:

/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sas(+0x16840e) [0x55cea7c3c40e]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sas(+0x4eddf) [0x55cea7b22ddf]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x2b9bf27a6ac4]
/usr/lib64/libpthread.so.0(+0xf630) [0x2b9bf1448630]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/saswzsu(wzsdoff+0xa5) [0x2b9c0490f2c5]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasxkern(ypfmtc+0x11f) [0x2b9bffa18cef]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasxkern(+0x7dc5d) [0x2b9bffa2ac5d]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasxkern(ypslf+0xa54) [0x2b9bffa29c54]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasxkern(ypmpstr+0x2e3) [0x2b9bffa25343]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasxkern(ypmstr+0x9d) [0x2b9bffa2504d]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasyh(+0x385a9) [0x2b9c044395a9]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasyh(+0x377b7) [0x2b9c044387b7]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasyh(yyhlock+0x8e5) [0x2b9c04434d15]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasyoio(yddel3+0xaba) [0x2b9c38b6bc8a]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/dbcs/sasexe/sasyoio(yddel2+0x60) [0x2b9c38b6b1b0]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sasimctr(import+0x3f3) [0x2b9c5588d993]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sasimctr(parsexe+0x24b) [0x2b9c5588cb9b]
/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sasimpor(sasimpor+0x275) [0x2b9c55382df5]
3 The SAS System Wednesday, May 18, 2022 10:17:00 AM

/apps/sas/sashome/dev/cmp/SASFoundation/9.4/sasexe/sas(vvtentr+0x18a) [0x55cea7b2293a]
/usr/lib64/libpthread.so.0(+0x7ea5) [0x2b9bf1440ea5]
/usr/lib64/libc.so.6(clone+0x6d) [0x2b9bf1e908dd]

Re: Running macro multiple times

Tom — Wed, 18 May 2022 17:40:13 GMT

Not good.

To debug that type of error run the PROC IMPORT step without any macro code involved.

Start a new SAS session to run the test.

In your code generation issue you will want to generate a different SAS dataset name for each XLSX file you read in. Otherwise the datasets generated by the earlier calls to the macro will be overwritten. One solution is to just include the YEARMONTH string into the name of dataset in addition to including it in the name of the XLSX file to be read.

Re: Running macro multiple times

kajal_30 — Wed, 18 May 2022 17:49:40 GMT

Proc import step is running fine out of macro.

secondly I tried running giving only yearmonth as macro variable name , in the log file names are resolving correctly but everything is getting replaced by last yearmonth call

Re: Running macro multiple times

Tom — Wed, 18 May 2022 18:13:08 GMT

You need to do something like this so that each PROC IMPORT step creates a different SAS dataset.

%let yearmonth=202201;
proc import
  datafile="something or other&yearmonth..xlsx"
  out=something&yearmonth.
 ...

Re: Running macro multiple times

kajal_30 — Wed, 18 May 2022 20:26:02 GMT

but when I am running this macro outside of the call execute keeping the same out = dataset name it is working absolutely fine.

Re: Running macro multiple times

Quentin — Wed, 18 May 2022 20:34:14 GMT

Please post:

Code where you run the macro 3 times, loading different excel files and it runs fine. i.e. show the 3 macro invocations.
Your current code where you use CALL EXECUTE to run the macro 3 times, and it does NOT work fine, but instead overwrites the output dataset.
The log from running the above code (#1 and #2), with options MPRINT turned on.

If the macro works when you write three macro calls, but does not work when generate three macro calls via CALL EXECUTE, then you are in good shape for debugging the problem.

The challenge will be to get your CALL EXECUTE to the point where it can generate the exact same macro calls you are writing. Assuming you are using %NRSTR() and single quotes on the CALL EXECUTE appropriately, the log should show the macro calls generated by CALL EXECUTE.

Re: Running macro multiple times

Tom — Wed, 18 May 2022 20:38:02 GMT

Then either your macro is more complex than you have shown or you are doing other steps between the macro calls when you are running it manually.

Re: Running macro multiple times

Quentin — Wed, 18 May 2022 20:43:49 GMT

I think the problem is likely in the attempt to use CALL EXECUTE.

The macro looks like it should work to me. It writes (and over-writes) work.&out_table, then at the end PROC APPENDS it to out.&out_table. It's bad style / risky to overwrite work.&out_table, but I think it should work when called three times in a row, including using CALL EXECUTE to generate the macro calls.

Re: Running macro multiple times

Tom — Wed, 18 May 2022 20:50:18 GMT

Using PROC APPEND with dataset generated from PROC IMPORT is another source of errors as you cannot depend on PROC IMPORT creating compatible dataset structures. But they would happen whether or not any code generation (macro call or CALL EXECUTE() function calls) was used to run the steps.

Re: Running macro multiple times

kajal_30 — Thu, 19 May 2022 14:09:44 GMT

This one is getting file name as

bnk_mkt_scr_201407,201508,201605,201706,201804,201905.xlsx'

but we need it like individual files

bnk_mkt_scr_201407.xlsx

bnk_mkt_scr_201508.xlsx and so on

Re: Running macro multiple times

Quentin — Thu, 19 May 2022 14:25:22 GMT

That suggests the problem is with how you are using CALL EXECUTE. Again, please show the CALL EXECUTE step you are running.

Re: Running macro multiple times

kajal_30 — Thu, 19 May 2022 16:09:31 GMT

I am able to run that macro by manually putting multiple values for macro variables. but not able to loop macro variable values automatically.

for eg:

%let dt = 201901

%let dt = 201902

%let dt = 201903

%let dt = 201904

but I want it like to be resolved automatically each time of invocation.

Re: Running macro multiple times

Tom — Thu, 19 May 2022 16:25:09 GMT

@kajal_30 wrote:

I am able to run that macro by manually putting multiple values for macro variables. but not able to loop macro variable values automatically.

for eg:

%let dt = 201901

%let dt = 201902

%let dt = 201903

%let dt = 201904

but I want it like to be resolved automatically each time of invocation.

????

Define the macro to take a PARAMETER.

Then when you CALL the macro pass in the value for the PARAMTER.

%macro mymacro(dt);
 .... &DT ....
%mend mymacro;
%mymacro(dt=201901)
%mymacro(dt=201902)
...

Or use a data step to call it multiple times.

So to call it with the 6 months starting 01JAN2019 you might use a data step like this.

data _null_;
  basedate='01JAN2019'd;
  do offset=0 to 5 ;
     date=intnx('month',basedate,offset);
     call execute('%nrstr(%mymacro)(dt=' || put(date,yymmn6.) || ')' );
  end;
run;