topic Re: Need a substitute for "add_months()" teradata function in sas in SAS Programming

Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 07:24:05 GMT

Hi,

Could anyone please help me with a substitute for below statement in sas.

%rd=5;

lts < add_months(CURRENT_TIMESTAMP, -12 * &rd.)

Thankyou

Re: Need a substitute for "add_months()" teradata function in sas

LinusH — Mon, 04 Sep 2023 07:44:18 GMT

I think the equivalent in SAS would be INTNX function using the 'MONTH' option:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lefunctionsref/p10v3sa3i4kfxfn1sovhi5xzxh8n.htm

Re: Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 09:01:19 GMT

Hi thankyou for your reply.I tried intnx but i am getting below error

where lts < intnx('month',CURRENT_TIMESTAMP, -12
SYMBOLGEN: Macro variable RTD resolves to 5
39 ! * &rtd.) )b;
ERROR: Function INTNX requires a numeric expression as argument 2.
ERROR: Expression using less than (<) has components that are of different data types.
ERROR: Function INTNX requires a numeric expression as argument 2.
ERROR: Expression using less than (<) has components that are of different data types.
ERROR: The following columns were not found in the contributing tables: CURRENT_TIMESTAMP.

what am i doing wrong

Re: Need a substitute for "add_months()" teradata function in sas

Patrick — Mon, 04 Sep 2023 10:00:22 GMT

CURRENT_TIMESTAMP is also Teradata syntax. Use the SAS Date or Datetime function instead.

data demo;
  SAS_datetime_value=intnx('dtmonth',datetime(),-12,'s');
  SAS_date_value=intnx('month',date(),-12,'s');
run;

proc print data=demo;
  format SAS_datetime_value datetime20. SAS_date_value date9.;
run;

Re: Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 10:57:46 GMT

@Patrick thankyou for your reply,datetime works but i am getting 0 records,is it the multiplication issue here?

where lts < intnx('month',datetime(), -12 * &rtncd.,"S")

where lts < intnx('month',datetime(), -12
SYMBOLGEN: Macro variable RTNCD resolves to 5
41 ! * &rtncd.,"S") )b;
NOTE: Invalid argument to function INTNX. Missing values may be generated.
NOTE: Invalid argument to function INTNX. Missing values may be generated.
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: Teradata connection: TPT FastExport has read 1066000 row(s).
NOTE: Table WORKDB.CNT_DLT_VLMS created, with 0 rows and 3 columns.

Re: Need a substitute for "add_months()" teradata function in sas

LinusH — Mon, 04 Sep 2023 11:22:15 GMT

Can you show the full log?

Also, since Teradata is involved, add this to your code:

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;

Re: Need a substitute for "add_months()" teradata function in sas

LinusH — Mon, 04 Sep 2023 11:25:37 GMT

Also, just to understand your query.
The current code will pick records that is 5 years or older.
And does "lts" have timestamp values or dates?

Re: Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 12:04:40 GMT

@LinusH yes the current code will pick the values before 5years.the dates present are in timestamp

Re: Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 12:05:59 GMT

here is the log-

1 The SAS System 04:02 Monday, September 4, 2023

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=ACTIVEX;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 FILENAME EGSR TEMP;
15 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16 STYLE=HtmlBlue
17 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome2/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18 NOGTITLE
19 NOGFOOTNOTE
20 GPATH=&sasworklocation
SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/tmp/SAS_work2ADC000020D2_cdpra00a0312/SAS_work8744000020D2_cdpra00a0312/"
21 ENCODING=UTF8
22 options(rolap="on")
23 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24
25 GOPTIONS ACCESSIBLE;
26 options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix;
27 proc sql;
28 drop table WORKDB.CNT_DLT_VLMS;

TERADATA_0: Prepared: on connection 11
SELECT * FROM D_FCDS_EAA."CNT_DLT_VLMS"

TERADATA: trforc: COMMIT WORK

TERADATA_1: Executed: on connection 11
DROP TABLE D_FCDS_EAA."CNT_DLT_VLMS"

TERADATA: trforc: COMMIT WORK
NOTE: Table WORKDB.CNT_DLT_VLMS has been dropped.
29 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds

30
31 proc sql;
32 create table WORKDB.CNT_DLT_VLMS as
33 select a.distinct_load_ts ,
34 a.volume_records,
35 b.to_delete_records
36 from (select load_ts as distinct_load_ts
37 ,count(load_ts) as volume_records
38 from FCORE_EE.peergrp
39 where load_ts < intnx('month',datetime(), -12
SYMBOLGEN: Macro variable RTNCD resolves to 5
39 ! * &rtncd.,"S")
40 group by 1 ) a
41 ,(select count(*) as to_delete_records
42 from FCORE_EE.peergrp
43 where load_ts < intnx('month',datetime(), -12
SYMBOLGEN: Macro variable RTNCD resolves to 5
43 ! * &rtncd.,"S") )b;

TERADATA_2: Prepared: on connection 15
SELECT * FROM libc."peergrp"

TERADATA_3: Prepared: on connection 16
SELECT * FROM libc."peergrp"

NOTE: Invalid argument to function INTNX. Missing values may be generated.
NOTE: Invalid argument to function INTNX. Missing values may be generated.

TERADATA_4: Prepared: on connection 17
select a."distinct_load_ts", a."volume_records", b."to_delete_records" from ( select TXT_1."LOAD_TS" as "distinct_load_ts", COUNT(TXT_1."LOAD_TS") as "volume_records" from "D_FCDS_S_FCCORE_E0"."peergrp" TXT_1 where TXT_1."LOAD_TS" < NULL
group by TXT_1."LOAD_TS" ) a, ( select COUNT(*) as "to_delete_records" from "libc"."peergrp" TXT_2 where TXT_2."LOAD_TS" < NULL ) b

TERADATA: trforc: COMMIT WORK
ACCESS ENGINE: ERROR: Teradata prepare: The user must use IS NULL or IS NOT NULL to test for NULL values. SQL statement was: select a."distinct_load_ts", a."volume_records", b."to_delete_records" from ( select TXT_1."LOAD_TS" as "distinct_load_ts",
COUNT(TXT_1."LOAD_TS") as "volume_records" from "libc"."peergrp" TXT_1 where TXT_1."LOAD_TS" < NULL group by TXT_1."LOAD_TS" ) a, ( select COUNT(*) as "to_delete_records" from "libc"."peergrp"
TXT_2 where TXT_2."LOAD_TS" < NULL ) b.
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.

TERADATA_5: Prepared: on connection 17
select TXT_1."LOAD_TS" as "distinct_load_ts", COUNT(TXT_1."LOAD_TS") as "volume_records" from "libc"."peergrp" TXT_1 where TXT_1."LOAD_TS" < NULL group by TXT_1."LOAD_TS"

TERADATA: trforc: COMMIT WORK
ACCESS ENGINE: ERROR: Teradata prepare: The user must use IS NULL or IS NOT NULL to test for NULL values. SQL statement was: select TXT_1."LOAD_TS" as "distinct_load_ts", COUNT(TXT_1."LOAD_TS") as "volume_records" from
"libc"."peergrp" TXT_1 where TXT_1."LOAD_TS" < NULL group by TXT_1."LOAD_TS".
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.

TERADATA_6: Prepared: on connection 17
select COUNT(*) as "to_delete_records" from "libc"."peergrp" TXT_2 where TXT_2."LOAD_TS" < NULL

TERADATA: trforc: COMMIT WORK
ACCESS ENGINE: ERROR: Teradata prepare: The user must use IS NULL or IS NOT NULL to test for NULL values. SQL statement was: select COUNT(*) as "to_delete_records" from "libc"."peergrp" TXT_2 where TXT_2."LOAD_TS" < NULL .
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

TERADATA_7: Prepared: on connection 11
SELECT * FROM D_FCDS_EAA."CNT_DLT_VLMS"

TERADATA: trforc: COMMIT WORK
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

TERADATA_8: Executed: on connection 17
CREATE MULTISET TABLE D_FCDS_EAA."CNT_DLT_VLMS" ("distinct_load_ts" TIMESTAMP(0),"volume_records" FLOAT,"to_delete_records" FLOAT);COMMIT WORK

NOTE: SAS threaded sort was used.
TERADATA: trqacol- No casting. Raw row size=19, Casted size=19, CAST_OVERHEAD_MAXPERCENT=20%

TERADATA_9: Prepared: on connection 15
SELECT "LOAD_TS" FROM libc."peergrp"

TERADATA: trforc: COMMIT WORK
TERADATA: trforc: COMMIT WORK
NOTE: Teradata connection: TPT FastExport has read 1066000 row(s).
TERADATA: trforc: COMMIT WORK
NOTE: Table WORKDB.CNT_DLT_VLMS created, with 0 rows and 3 columns.

44 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 2.79 seconds
cpu time 0.35 seconds

45
46 GOPTIONS NOACCESSIBLE;
47 %LET _CLIENTTASKLABEL=;
48 %LET _CLIENTPROCESSFLOWNAME=;
49 %LET _CLIENTPROJECTPATH=;
50 %LET _CLIENTPROJECTPATHHOST=;
51 %LET _CLIENTPROJECTNAME=;
52 %LET _SASPROGRAMFILE=;
53 %LET _SASPROGRAMFILEHOST=;
54
55 ;*';*";*/;quit;run;
56 ODS _ALL_ CLOSE;
57
58
59 QUIT; RUN;
60

Re: Need a substitute for "add_months()" teradata function in sas

LinusH — Mon, 04 Sep 2023 12:41:50 GMT

If your source columns are in timestamp (detetime in SAS, you need to prefix your interval with DT:
"Interval names that are used with SAS date values can be prefixed with 'DT' to construct interval names for use with SAS datetime values. The interval names DTYEAR, DTSEMIYEAR, DTQTR, DTMONTH, DTSEMIMONTH, DTTENDAY, DTWEEK, DTWEEKDAY, and DTDAY are used with SAS time or datetime values."

Re: Need a substitute for "add_months()" teradata function in sas

Coco_08 — Mon, 04 Sep 2023 12:39:49 GMT

HI @LinusH I just checked again the the dates are in datetime19. format

Re: Need a substitute for "add_months()" teradata function in sas

Tom — Mon, 04 Sep 2023 13:14:09 GMT

@Coco_08 wrote:

@LinusH yes the current code will pick the values before 5years.the dates present are in timestamp

So if you have DATETIME values you need to use DT intervals with the INTNX() function to let it know it needs to do the adjustment in SECONDS and not in DAYS.

If you want to move by years then use the DTYEAR interval. So 5 years before now would be :

intnx('dtyear',datetime(),-5,'s')

You might want to express that as a datetime constant. That might allow for faster performance if indexes are involved. To do that use the %SYSFUNC() macro function to call the INTNX() and DATETIME() functions.

This will generate the datetime value as a number which should work fine in comparisons even if it looks funny to humans.

%sysfunc(intnx(dtyear,%sysfunc(datetime())),-5,s)))