topic Re: Macro Function Invoking using SQL Table in SAS Enterprise Guide

Macro Function Invoking using SQL Table

akshaybatra1 — Thu, 15 Sep 2016 09:20:21 GMT

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 &period;
%put &start_date;

%mend myprep;

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

run;

Re: Macro Function Invoking using SQL Table

Reeza — Thu, 15 Sep 2016 09:20:20 GMT

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

The documentation has good examples on usage.

Re: Macro Function Invoking using SQL Table

RW9 — Thu, 15 Sep 2016 09:21:05 GMT

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.

Re: Macro Function Invoking using SQL Table

akshaybatra1 — Thu, 15 Sep 2016 09:21:43 GMT

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.

Re: Macro Function Invoking using SQL Table

Reeza — Thu, 15 Sep 2016 09:24:15 GMT

@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.

Re: Macro Function Invoking using SQL Table

akshaybatra1 — Thu, 15 Sep 2016 09:24:45 GMT

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.

Re: Macro Function Invoking using SQL Table

RW9 — Thu, 15 Sep 2016 09:31:15 GMT

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.

Re: Macro Function Invoking using SQL Table

akshaybatra1 — Thu, 15 Sep 2016 09:45:17 GMT

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.

Re: Macro Function Invoking using SQL Table

Reeza — Thu, 15 Sep 2016 09:52:33 GMT

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.

Re: Macro Function Invoking using SQL Table

Kurt_Bremser — Thu, 15 Sep 2016 10:28:37 GMT

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.

Re: Macro Function Invoking using SQL Table

akshaybatra1 — Thu, 15 Sep 2016 11:26:01 GMT

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

Thanks a lot.

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

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