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
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;
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.
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;
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
That suggests the problem is with how you are using CALL EXECUTE. Again, please show the CALL EXECUTE step you are running.
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-dat... 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.
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.
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.
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]
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.
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
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.
...
but when I am running this macro outside of the call execute keeping the same out = dataset name it is working absolutely fine.
Please post:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.