Pass macro variables Macro variables to Remote connection(within RSUBMIT)

[Santt0sh]

Hi All,

I am trying to pass the macro variables created on local to Remote connection.

The macro variables are dates (start dates and End dates) parameters to extract data from Teradata for the mentioned period. Month(12 months) wise data needs to be extracted from Teradata, i can create 2 date parameters for start and end date and extract the data for entire period of 12 months.

but it is taking a lot lot of time for extraction and once the extraction is complete i need to perform various calculations and generate reports.

Hence i tried to split the start date and end date month wise, and the extraction is faster for months. as the this extraction and calculation will performed in a loop for 12 months.

Since I am not able to pass the Macro variables to Remote (using %SYSLPUT) , i am running the Macro within Rsubmit/ENDRSUBMIT.

/*start of Macro*/

%MACRO SRTDT(BGNDTE=);
%IF %LENGTH(&BGNDTE.) GE 1 %THEN
%DO;
%PUT "START DATE IS NOT MISSING CALCULATING DATES FROM THE GIVEN START DATE";

DATA _NULL_;
INDTE = INPUT("&BGNDTE.",YYMMN6.);
PUT INDTE YYMMN6.;
CALL SYMPUT('INSTDT',(PUT(INTNX('MONTH',INDTE, -12,'B') ,YYMMDD10.)));
CALL SYMPUT('INEDDT',(PUT(INTNX('MONTH',INDTE, 0,'E') ,YYMMDD10.)));
RUN;

%PUT "STARRT DATE ==>> &INSTDT."; /* BGNDTE=202109 "STARRT DATE ==>> 2020-10-01"*/
%PUT "END DATE ==>> &INEDDT."; /*BGNDTE=202109 "END DATE ==>> 2021-09-30"*/
%LET DIFF = %SYSFUNC(INTCK(MONTH,%SYSFUNC(INPUTN(&INSTDT., YYMMDD10.)),%SYSFUNC(INPUTN(&INEDDT., YYMMDD10.))));
%PUT "TOTAL# MONTHS ===>> &DIFF.";
%END;
%ELSE
%DO;
%PUT "START DATE IS MISSING CALCULATING DATES FROM THE SYSTEM DATE.";

DATA _NULL_;
CALL SYMPUT('INSTDT',(PUT(INTNX('MONTH',TODAY(), -13,'B') ,YYMMDD10.)));
CALL SYMPUT('INEDDT',(PUT(INTNX('MONTH',TODAY(), 0,'E') ,YYMMDD10.)));
RUN;

%PUT "STARRT DATE ==>> &INSTDT.";
%PUT "END DATE ==>> &INEDDT.";
%LET DIFF = %SYSFUNC(INTCK(MONTH,%SYSFUNC(INPUTN(&INSTDT., YYMMDD10.)),%SYSFUNC(INPUTN(&INEDDT., YYMMDD10.))));
%PUT "TOTAL# MONTHS ===>> &DIFF.";

%DO i = 1 %TO &DIFF.;

DATA _NULL_;
CALL SYMPUT("SRTDT&i.",(PUT(INTNX('MONTH',%SYSFUNC(INPUTN(&INSTDT., YYMMDD10.)), -&i.,'B') ,YYMMDD10.)));
CALL SYMPUT("ENDDT&i.",(PUT(INTNX('MONTH',%SYSFUNC(INPUTN(&INSTDT., YYMMDD10.)), -&i.,'E') ,YYMMDD10.)));
RUN;

%LET MNTH&i.= %SYSFUNC(INPUTN(&&SRTDT&i., YYMMDD10.),YYMMN6.);
%PUT MONTH ===>>> &&MNTH&i.;
%PUT SRTDT&i. ==>> &&SRTDT&i. ENDDT&i. ==>> &&ENDDT&i.;
%PUT SRTDT&i. ==>> &&SRTDT&i. ENDDT&i. ==>> &&ENDDT&i.;
%let Startdtact&i. = %BQUOTE('&&SRTDT&i.');
%PUT &&Startdtact&i.;
%let Enddtact&i.=%BQUOTE('&&ENDDT&i.');
%let Enddt&i.= %BQUOTE('&&ENDDT&i.');
%put &&Startdtact&i. &&Enddtact&i. &&Enddt&i.;
%END;
%END;
%MEND;

%SRTDT(BGNDTE= );

Kindly let me know if we have any other means to pass the macro variables to Remote connection.

[PaigeMiller]

Please, for this problem and from now on, post your code in a code box (click on the "little running man" icon), and indent the code properly to make it readable. This helps everyone, and that helps you.

[Santt0sh]

Hi Paige,

I would be careful the next time while posting a piece of code. Unfortunately im not aware how do i repost my code as you suggested.
Thank you i will make sure i will the options while posting the code.

Regards,

[ballardw]

I don't see anyplace that you are attempting to actually send those macro variables to the remote connection. I do see a macro definition and a call to the macro that will not create any output.

So, how are currently intending to send those values to the remote connection?

[Santt0sh]

Hi,

Yes i am trying to send these to a remote connection.
I tried using %syslput. but got an error %syslput appears as text.
and also tried assigning as %Global variables i got an error stating local variables cant be global.

[Santt0sh]

Hi,

I am currently running the Rsubmit /ENDRSUBMIT Within a macro.

[SASKiwi]

Its not a good idea to have RSUBMIT blocks within a macro as the remote SAS session has no idea it is running inside a macro which can cause unexpected processing and logic problems, including passing macro variables between SAS sessions.

[SASKiwi]

Why can't you use %SYSLPUT? If it is because you are creating the macro variables inside a macro, just define them as GLOBAL first using the %GLOBAL statement. Then they will be available to use with %SYSLPUT.

[Santt0sh]

Hi,

Thank you for your quick response!
As macro variables need to be looped, i am not sure if it will work?

[Santt0sh]

a Where Condition fails because of the dates.


DT between &&Startdtact&i. and &&Enddtact&i.
WARNING: Apparent symbolic reference I not resolved.
WARNING: Apparent symbolic reference I not resolved.
WARNING: Apparent symbolic reference I not resolved.

[SASKiwi]

Please post your complete SAS log - its impossible to understand what is happening in your code if you just post bits of it. Run your program using this statement: options mprint; That will enable us to see your source statements.

Also use the </> menu option when posting SAS logs so we can read them more easily.

[average_joe · Accepted solution]

Not sure why you cannot use %SYSLPUT, but here is some cleaner code to generate dates and macro variables.

data _null_;
* create monthly dates for the 12 months prior to the current month;
yr_end_date = intnx('month', date(), -1, 'e');
yr_start_date = intnx('month', yr_end_date, -11);
put yr_start_date= yymmdd10. yr_end_date= yymmdd10.;
do until (yr_start_date > yr_end_date);
    i + 1;
    mnth_start_date = yr_start_date;
    mnth_end_date = intnx('month', mnth_start_date, 0, 'e');
    put mnth_start_date= yymmdd10. mnth_end_date= yymmdd10.;
    call symput(cats('mnth', i), put(mnth_start_date, yymmn6.));
    call symput(cats('mnthstdt', i), quote(put(mnth_start_date, yymmdd10.),"'"));
    call symput(cats('mnthenddt', i), quote(put(mnth_end_date, yymmdd10.),"'"));
    yr_start_date = intnx('month', yr_start_date, 1);
end;
run;

%macro test_dates;
%do i = 1 %to 12;
    %put &i  &&mnth&i  &&mnthstdt&i  &&mnthenddt&i;
%end;
%mend;

%test_dates;

The macro output:

1  202104  '2021-04-01'  '2021-04-30'
2  202105  '2021-05-01'  '2021-05-31'
3  202106  '2021-06-01'  '2021-06-30'
4  202107  '2021-07-01'  '2021-07-31'
5  202108  '2021-08-01'  '2021-08-31'
6  202109  '2021-09-01'  '2021-09-30'
7  202110  '2021-10-01'  '2021-10-31'
8  202111  '2021-11-01'  '2021-11-30'
9  202112  '2021-12-01'  '2021-12-31'
10  202201  '2022-01-01'  '2022-01-31'
11  202202  '2022-02-01'  '2022-02-28'
12  202203  '2022-03-01'  '2022-03-31'

[Santt0sh]

Hi Joe,

Thank you for the post!
I am trying to call the macro variables, in a Remote Session to pull the data for month and its not working for.

DT between &&Startdtact&i. and &&Enddtact&i. in a pass thru (SQL).

Regards

[Santt0sh]

Hi,

Ill try to clarify what i am trying to achieve.
Extract data for every month(01jan2000 - 31jan2000) from Teradata and do some calculations create new variables for the calculated values and do this for the next 11 months. and append all the Jan 2000 to Dec 2000 data sets. run a proc summary and generate reports.

Normally in a macro or a sas data step it would be a Do loop with 'i' iterating till the end of the loop.
can you please suggest me how do i loop these macro variables created &&Startdtact&i. and &&Enddtact&i..
01012000 to 01312000
from xyz
where DT between &&Startdtact&i. and &&Enddtact&i.;
;

and later i need to process this dataset(Jan2000) for other calculations within the loop.

Regards,

[Tom]

That still doesn't have anything to RSUBMIT.

Are you perhaps thinking about using PROC SQL to submit SQL code to run in TERADATA?

If you want to use macro variables to generate TERADATA code that refer to date literals then make sure to include the single quotes in the values of the macro variables.

%let start=%bquote('%sysfunc(today(),yymmdd10.)');
proc sql;
connect to teradata ... ;
create table local as select * from connection to teradata
(select * from mytable where mydate = &start)
;
quit;