topic Re: Trying to create a table from Teradata using SAS macros. in SAS Programming

Trying to create a table from Teradata using SAS macros.

Santt0sh — Fri, 14 Oct 2022 18:36:32 GMT

Hi All,

I am trying to create a dates table for every 3 months, when i am using the below code within Rsubmit the datesqtr datasets is populating as expected. but when i use the same code outside Rsubmit the datesqtr dataset is not populating as expected. Could you please suggest?

DATA _NULL_;
	YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B');
	YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12);

	DO UNTIL(YR_END_DATE>YR_START_DATE);
		i+1;
		MNTH_START_DATE=YR_START_DATE;
		MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e');
		PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S');

		/*PREVIOUS MONTHS DATE*/
		PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10.  PREVDTE=YYMMDD10.;
		CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.));
		CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.));

		/*start date current month i.e. reporting period month t-1 month*/
		CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.));

		/*end date current month i.e. reporting period month t-1 month*/
		/*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/
		CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.));

		/*atm transaction for the reporting month month*/
		/*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/
		CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'), 
			YYMMDD10.));

		/*previous month date*/
		CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'), 
			YYMMDD10.));

		/*match to fetch the qtrly start date from the source format*/
		CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'), 
			YYMMDD10.));

		/*match to fetch the end qtrly date from the source format*/
		CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.));

		/*transaction for previous months*/
		CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.));

		/*atm transaction for previous months*/
		/*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/
		YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1);
	END;
	PUT YR_START_DATE YR_END_DATE;
RUN;

%macro test;
	%do i=1 %to 4;
		%let stdte&i. = %sysfunc(intnx(month, %sysfunc(today()), -&i.), 6.);
		%put stdte&i. = &&stdte&i.;
		%let endte&i. =  %eval(%sysfunc(intnx(month, &&stdte&i., 3), 6.)-1);
		%put endte&i. = &&endte&i.;

		proc sql;
			connect to teradata (user="&myname.@xxxx" 
				pass=&mypwd. tdpid=&id. mode=teradata);
			create table datesqtr as select * from connection to teradata
(select date_key, cldte_sas from xxxx.ss_dte_dim where cldte_sas 
				between &&stdte&i. - 4 and &&endte&i.
order by cldte_sas;
			) order by cldte_sas;
			disconnect from teradata;
		quit;

	%end;
%mend;

%test;

Re: Trying to create a table from Teradata using SAS macros.

Tom — Fri, 14 Oct 2022 18:43:19 GMT

How does RSUBMIT have anything to do with this?

What are you rsubmitting?

Where are you rsubmitting it from?

Where are you rsubmitting it to?

If you want to get a range of dates in Teradata use the built in CALENDER.

https://docs.teradata.com/r/Teradata-VantageTM-Data-Dictionary/March-2019/Data-Dictionary-Views/Using-System-Views/System-Calendar-View/Example-Using-Sys_Calendar.Calendar

Re: Trying to create a table from Teradata using SAS macros.

Santt0sh — Fri, 14 Oct 2022 18:49:56 GMT

Hi Tom,

Thank you for your quick response.
I am not sure what's going wrong when i run the code outside rsubmit.
1. What are you rsubmitting?
Rsubmit;
%macro test;
Above Code...

%mend;
%test;
Endrsubmit;
The sas code shared works when executted using Rsubmit, and doest work when i tried out side the Rsubmit.

I will try to use the link provided Tom.

Re: Trying to create a table from Teradata using SAS macros.

Tom — Fri, 14 Oct 2022 19:02:42 GMT

What is the SAS code you are trying to generate?

What does this code do?

create table datesqtr as
    select * from connection to teradata
(select date_key
      , cldte_sas
 from xxxx.ss_dte_dim
  where cldte_sas between &STDTE1 - 4 and &ENDTE1 
  order by cldte_sas
);

What type of values does CLDTE_SAS contain?

What type of values do the macro variables STDTE1 and ENDTE1 contain?

Why are you also creating STDTE2, STDTE3 and STDTE4? That is just making your macro code more complicated without any added value.

Re: Trying to create a table from Teradata using SAS macros.

Tom — Fri, 14 Oct 2022 19:05:47 GMT

If you don't also rsubmit the rest of the code where it the macro going to get the macro variables?

Why just RSUBMIT the query instead?

%macro test;
Rsubmit;
proc sql....

endrsubmit;
%mend;
%test;

Re: Trying to create a table from Teradata using SAS macros.

Reeza — Fri, 14 Oct 2022 19:12:37 GMT

Post the log from the code. RSUBMIT sends the code to a server. Most likely you do not have access to the teradata server locally but the server does have access is my first guess.

You need to run all the code either locally or on the server, you cannot do some locally and some on the server.

Re: Trying to create a table from Teradata using SAS macros.

Santt0sh — Fri, 14 Oct 2022 19:19:04 GMT

CLDTE_SAS contains = 22,732
22733 and so on. (SAS Date)

SAS LOGS Run local not Rsubmit;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 DATA _NULL_;
70 YR_START_DATE=INTNX('MONTH', DATE(), -1, 'B');
71 YR_END_DATE=INTNX('MONTH', YR_START_DATE, -12);
72
73 DO UNTIL(YR_END_DATE>YR_START_DATE);
74 i+1;
75 MNTH_START_DATE=YR_START_DATE;
76 MNTH_END_DATE=INTNX('MONTH', MNTH_START_DATE, 0, 'e');
77 PREVDTE=INTNX('MONTH', MNTH_START_DATE, -1, 'S');
78
79 /*PREVIOUS MONTHS DATE*/
80 PUT MNTH_START_DATE=YYMMDD10. MNTH_END_DATE=YYMMDD10. PREVDTE=YYMMDD10.;
81 CALL SYMPUT(CATS('MNTH', I), PUT(MNTH_START_DATE, YYMMN6.));
82 CALL SYMPUT(CATS('MNTHSTDT', I), PUT(MNTH_START_DATE, YYMMN6.));
83
84 /*start date current month i.e. reporting period month t-1 month*/
85 CALL SYMPUT(CATS('MNTHENDDT', I), PUT(MNTH_END_DATE, YYMMN6.));
86
87 /*end date current month i.e. reporting period month t-1 month*/
88 /*FIRST MONTHS DATE YYMM AS TRANSACTION DATE*/
89 CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.));
90
91 /*atm transaction for the reporting month month*/
92 /*PREVIOUS MONTH DATE YYMM AS TRANSACTION DATE*/
93 CALL SYMPUT(CATS('PREVDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -1, 'S'),
94 YYMMDD10.));
95
96 /*previous month date*/
97 CALL SYMPUT(CATS('BGNDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -3, 'S'),
98 YYMMDD10.));
99
100 /*match to fetch the qtrly start date from the source format*/
101 CALL SYMPUT(CATS('ENDDTE', I), PUT(INTNX('MONTH', MNTH_START_DATE, -2, 'E'),
102 YYMMDD10.));
103
104 /*match to fetch the end qtrly date from the source format*/
105 CALL SYMPUT(CATS('DATE', I), PUT(MNTH_START_DATE, YYMMN6.));
106
107 /*transaction for previous months*/
108 CALL SYMPUT(CATS('ATMDATE', I), PUT(PREVDTE, YYMMN6.));
109
110 /*atm transaction for previous months*/
111 /*PREVIOUS MONTH DATE YYMMDD AS TRANSACTION DATE*/
112 YR_START_DATE=INTNX('MONTH', YR_START_DATE, -1);
113 END;
114 PUT YR_START_DATE YR_END_DATE;
115 RUN;

MNTH_START_DATE=2022-09-01 MNTH_END_DATE=2022-09-30 PREVDTE=2022-08-01
MNTH_START_DATE=2022-08-01 MNTH_END_DATE=2022-08-31 PREVDTE=2022-07-01
MNTH_START_DATE=2022-07-01 MNTH_END_DATE=2022-07-31 PREVDTE=2022-06-01
MNTH_START_DATE=2022-06-01 MNTH_END_DATE=2022-06-30 PREVDTE=2022-05-01
MNTH_START_DATE=2022-05-01 MNTH_END_DATE=2022-05-31 PREVDTE=2022-04-01
MNTH_START_DATE=2022-04-01 MNTH_END_DATE=2022-04-30 PREVDTE=2022-03-01
MNTH_START_DATE=2022-03-01 MNTH_END_DATE=2022-03-31 PREVDTE=2022-02-01
MNTH_START_DATE=2022-02-01 MNTH_END_DATE=2022-02-28 PREVDTE=2022-01-01
MNTH_START_DATE=2022-01-01 MNTH_END_DATE=2022-01-31 PREVDTE=2021-12-01
MNTH_START_DATE=2021-12-01 MNTH_END_DATE=2021-12-31 PREVDTE=2021-11-01
MNTH_START_DATE=2021-11-01 MNTH_END_DATE=2021-11-30 PREVDTE=2021-10-01
MNTH_START_DATE=2021-10-01 MNTH_END_DATE=2021-10-31 PREVDTE=2021-09-01
MNTH_START_DATE=2021-09-01 MNTH_END_DATE=2021-09-30 PREVDTE=2021-08-01
22493 22524
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 647.03k
OS Memory 23972.00k
Timestamp 10/14/2022 07:17:46 PM
Step Count 24 Switch Count 0
Page Faults 0
Page Reclaims 238
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 8

116
117 %macro test;
118 %do i=1 %to 4;
119 %let stdte&i. = %sysfunc(intnx(month, %sysfunc(today()), -&i.), 6.);
120 %put stdte&i. = &&stdte&i.;
121 %let endte&i. = %eval(%sysfunc(intnx(month, &&stdte&i., 3), 6.)-1);
122 %put endte&i. = &&endte&i.;
123
124 /* proc sql; */
125 /* connect to teradata (user="&myname.@xxxx" */
126 /* pass=&mypwd. tdpid=&id. mode=teradata); */
127 /* create table datesqtr as select * from connection to teradata */
128 /* (select date_key, cldte_sas from xxxx.ss_dte_dim where cldte_sas */
129 /* between &&stdte&i. - 4 and &&endte&i. */
130 /* order by cldte_sas; */
131 /* ) order by cldte_sas; */
132 /* disconnect from teradata; */
133 /* quit; */
134
135 %end;
136 %mend;
137
138 %test;
stdte1 = 22889
endte1 = 22979
stdte2 = 22858
endte2 = 22949
stdte3 = 22827
endte3 = 22918
stdte4 = 22797
endte4 = 22888
139
140 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
150

Re: Trying to create a table from Teradata using SAS macros.

Santt0sh — Fri, 14 Oct 2022 19:25:18 GMT

Hi Reeza,

Thank you for your quick response!
Unfortunately, I can't post the Rsubmit logs, Apologies.
I have access to Teradata when I try running previously used code where the values are hardcoded for a period every 3 months in the Code i am able to create the required datasets, on my local.

Re: Trying to create a table from Teradata using SAS macros.

Reeza — Fri, 14 Oct 2022 19:28:05 GMT

If you can't post the logs, makes sure to set the macro debugging options on and run it in each set up and compare the two logs to see where they're different.

Re: Trying to create a table from Teradata using SAS macros.

Santt0sh — Fri, 14 Oct 2022 19:30:50 GMT

Hi Reeza,

That's was what I am trying to do.

Thank you and I will keep posted

Re: Trying to create a table from Teradata using SAS macros.

Tom — Fri, 14 Oct 2022 20:06:59 GMT

Why would you store the SAS's internal representation of a date as a numeric field in Teradata?

Why no make the variable a DATE field in Teradata?

I am still not clear what the purpose of the Teradata query is. Especially why you run it 4 different times.

Why run it four different times and overwrite the same output dataset each time?
Why not just skip the first three runs since you are throwing them away?

Re: Trying to create a table from Teradata using SAS macros.

Patrick — Sat, 15 Oct 2022 11:54:53 GMT

Your macro test as posted doesn't really work as you describe it does. How much did you change your real code?

%macro test;
  %do i=1 %to 4;
    %let stdte&i. = %sysfunc(intnx(month, %sysfunc(today()), -&i.), 6.);
    %let endte&i. =  %eval(%sysfunc(intnx(month, &&stdte&i., 3), 6.)-1);
    %put stdte&i. = %sysfunc(putn(&&stdte&i.,date9.)) endte&i. = %sysfunc(putn(&&endte&i.,date9.));

    /**
    proc sql;
      connect to teradata (user="&myname.@xxxx" pass=&mypwd. tdpid=&id. mode=teradata);
      create table datesqtr as 
        select * 
          from connection to teradata
          (
            select date_key, cldte_sas 
            from xxxx.ss_dte_dim 
            where cldte_sas between &&stdte&i. - 4 and &&endte&i.
            ;
          ) 
          order by cldte_sas;
      disconnect from teradata;
    quit;
    **/

  %end;
%mend;

%test;

Issues in above macro logic

1. The time periods are overlapping

2. The target table name is a fix string and though gets overwritten (re-created) by every single iteration of the loop. You need to use a dynamic table name instead like datesqtr_&i.

3. In the posted code there is an order by on the Teradata side and on the SAS side. Don't sort twice - that's just inefficient. Sort on the SAS side so the SAS table created gets the sort attribute set.

4. If this where clause works then "someone" must have stored SAS Date values in Teradata just as numbers instead of actual dates. This can work but.... why would one do that and loose all the date information and related functionality?

And last but not least the rsubmit story

One reason why one would use rsubmit is: You have a locally installed SAS where you don't have access to Teradata, you have a remote SAS Server that has access to Teradata and you want to run your SAS script from the locally installed SAS. In such a case you would create a remote session via rsubmit where you run your code BUT if you then want to use the table in your local SAS session you need to either download the table or use remote library services.

I would also expect that in such a scenario there are some additional part to rsubmit that you haven't shared - rsubmit <and here more stuff>; ...and a signon statement? etc.

Re: Trying to create a table from Teradata using SAS macros.

Santt0sh — Tue, 18 Oct 2022 13:42:16 GMT

Hi Patrick,

Thank you for your quick response!!
I will modify my code and follow the above.

Regards,
Santosh