BookmarkSubscribeRSS Feed
Coco_08
Calcite | Level 5

Hi,

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

%rd=5;

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

 

Thankyou

11 REPLIES 11
LinusH
Tourmaline | Level 20

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

Data never sleeps
Coco_08
Calcite | Level 5

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

Patrick
Opal | Level 21

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;

Patrick_0-1693821617165.png

 

Coco_08
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

Can you show the full log?

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

options msglevel=i sastrace=',,,d' sastraceloc=saslog nostsuffix; 
Data never sleeps
Coco_08
Calcite | Level 5

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

LinusH
Tourmaline | Level 20
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?
Data never sleeps
Coco_08
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
Coco_08
Calcite | Level 5

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

Tom
Super User Tom
Super User

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

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1326 views
  • 1 like
  • 4 in conversation