Macro Function Invoking using SQL Table

[akshaybatra1]

Hello,

I would like to invoke the macro function dynamically using the data from SQL table  but i am not sure why the last part of my code is not working.

%let start_date = '31Mar2008'd;
%let end_date = '30Apr2008'd;

DATA work.TMP_1;
FORMAT start_dt DATETIME.;
FORMAT end_dt DATETIME.;
FORMAT stdt DATE9.;
start_dt=DHMS(&start_date,0,0,0);
end_dt= DHMS(&end_date,0,0,0);
date_dif = intck('month',datepart(start_dt), datepart(end_dt));
/* stdt = intnx('month',datepart(start_dt),1,'same');*/
RUN;

PROC SQL NOPRINT;
SELECT
date_dif INTO: COUNT_VAR
FROM WORK.TMP_1;
QUIT;

%LET COUNT_VAR=%TRIM(&COUNT_VAR.);
%PUT &COUNT_VAR.;

PROC SQL NOPRINT;
DROP TABLE work.TMP2;
CREATE TABLE work.TMP2
(
date NUM FORMAT = DATE9.);
QUIT;

%MACRO test (COUNT_VAR=);
%DO I=0 %TO &COUNT_VAR.;
PROC SQL;
INSERT INTO tmp2
SELECT intnx('month',datepart(start_dt),&i,'same') AS date FORMAT=DATE9.
FROM TMP_1;
QUIT;

%END;
%MEND DEFAULT_12_MONTH;

OPTIONS MPRINT;
%test(COUNT_VAR=&COUNT_VAR.);

data tmp2;
set tmp2;
format cohort DATETIME.;
cohort = DHMS(date,0,0,0);
run;

proc sql ;
create table cohort_table as
select
'12' as period ,
INPUT(PUT(DATEPART(cohort),YYMMN6.), Z6.) AS start_date
from tmp2;
quit;

PROC SQL NOPRINT;
SELECT start_date INTO
:ST_DT_1 - :ST_DT_%eval(&COUNT_VAR.+1)
FROM cohort_table;
QUIT;

%put &st_dt_1.;

%macro myprep(period,start_date);
%put .
%put &start_date;

%mend myprep;

data _null_;
DO j = 1 to &count_var.;
%myprep(12,&st_dt_j);
end;

run;

[Reeza]

When trying to call/execute a macro from a data step use DOSUBL() or CALL EXECUTE(). 

The documentation has good examples on usage. 

[akshaybatra1]

Thanks for your reply,

I tried using 

call execute('%myprep(period'||period||',start_date'||start_date||');');
 call execute('%nrstr(%myprep('||period||','||start_date||'))');

it doesnt work aswell.

[Reeza]

---

@akshaybatra1 wrote:

Thanks for your reply,

I tried using 

call execute('%myprep(period'||period||',start_date'||start_date||');');
 call execute('%nrstr(%myprep('||period||','||start_date||'))');

it doesnt work aswell.

---

Then you did it incorrectly. 

Generate the macro command as a string variable first - make sure it's valid SAS code and then pass that string to call execute. 

[RW9]

Hi,

Sorry, its very difficult to read that code with no formatting and all upper case.  What is it your trying to accomplish.  Post some simple test data (in the form of a datastep), and what the output should look like.  You can "conditionally" execute code by using call execute:

data _null_;
  set sashelp.class;
  call execute(cats('%some_macro (',age,');'));
run;

This will call %some_macro for each observation in sashelp.class for instance.  However most of the code you provide seems redundant, hence why I want to see start and end position, there will be far easier and better ways of doing it.

[akshaybatra1]

if you run the code in SAS , except the macro, it will give you a table COHORT_TABLE. this  has 

Period  Start_date

12        200803
12        200804

I would like to pass these parameters into macro function dynamically.

[RW9]

Yes, but the question is why do you need to pass data into macro?  Macro language is a text based pre-compilation text generator - it does not do any processing, nor does it have datatypes/structures for processing data, that is what Base SAS is for.  It sounds like you are trying to emulate functionality already within Base SAS in Macro Language, which is never a good thing.  If you post what you are doing with that data, there will be a better method, either merging, re-structuring data etc.

[akshaybatra1]

I have a macro %myprep(period, start_date) where we need to pass parameters for every cohort, so ideally users have to write in the program  multiple times, as below.

%myprep(12,'201012')
%myprep(12,'201101')
%myprep(12,'201102')
%myprep(12,'201103')
%myprep(12,'201104') 

My program creates a SQL table 

period     Start_date

12            201012

12            201101

12            201102

I would like to use this table and pass it into function %myprep(period, start_date) so that we dont need to invoke macro function multiple times.

[Reeza · Accepted solution]

Why doesn't your macro loop to do the insert based on start and end date. 

If you want a table driven macro call execute is correct. As I stated before generate the string variable and pass that to call execute. 

For starters Your previous attempt had the words period and start date when those are not in your macro call. 

Umtested but easy to debug

Str= catt('%myprep(, period, ',', quote(start_date), ');');

call execute(Str);

steing needs to match macro call shown earlier, quotes included. 

[akshaybatra1]

Super !! It works, i just changed it in a way i need it.

Thanks a lot.

%macro myprep(period,start_date);
%put .
%put &start_date;
run;
%mend myprep;

data _null_;
set cohort_table;
Str= catt('%myprep(',12, ',',start_date, ');');
call execute(Str);
run;

[Kurt_Bremser]

It helps when you are consistent in the way you hand over parameters to macros

This

%myprep(12,'201012')

will complicate your work unnecessarily

Change your macro so that the (double) quotes are placed around the string where necessary, so you can write the string raw without quotes into the macro call. That also makes calling it with call execute easier.

Next, contemplate if it would not be better to use real SAS data values when you are dealing with what is basically a date. Within that context, SAS dates can be stored in macro variables with their raw numerical values (days from 01jan1960), which also makes quoting unnecessary.