how to increment datetime variable with 5 years

[Coco_08]

Hi,Below is the code from teradata passthrough that i am trying to convert to proc sql.Could you please help and let me know how to use the highlighted increment code.I tried intnx() but it is not working and giving me missing values.Thankyou

proc sql;

 create table PRE_QA_&v2. as

           select distinct b.distinct_load_ts,  

                   b.volume_records,

                   &tn. as table_name,

b.distinct_load_ts  + &rtncd. * interval '1' year as aged_out,

   &today_date.  as today_date,

           case when a.&v. = b.&v. then 'Y'

                         else 'N'

                         end   as flag_to_delete 

              from (select distinct_load_ts,

                           volume_records,

                           volume_before,

   &v.

                      from vlm_before_&v2. ) b  

left join

                   dist_ts_all_&v2.  a

on a.&v. = b.&v.;

quit;

[Patrick]

If all your source tables are in Teradata and you've got already working Teradata SQL then why don't you just use explicit pass-through SLQ because then you don't have to change your code.

[Kurt_Bremser]

Your INTNX should look like this:

intnx("dtyear",b.distinct_load_ts,&rtncd.,"s") as aged_out,

[Coco_08]

Hi,

26 proc sql;
27 create table PRE_QA_PEER as
28 select a.distinct_load_ts_all as distinct_load_ts
29 ,a.volume_records_all as volume_records
30 ,'FCORE_EE.FSC_ENTITY_PEER_GROUP' as table_name
31 , %sysfunc(intnx("dtyear",b.distinct_load_ts,&rtncd.,"s")) as aged_out
SYMBOLGEN: Macro variable RTNCD resolves to 5
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
32 ,"&today_date" as today_date
SYMBOLGEN: Macro variable TODAY_DATE resolves to '2023-09-05 18:56:07'
33 ,case when a.distinct_load_ts_all = b.distinct_load_ts then 'Y'
34 else 'N'
35 end as flag_to_delete from
36 (select distinct_load_ts_all,volume_records_all from VLM_BEFORE_PEER) a
37 left join
38 (select distinct_load_ts from CNT_DLT_VLMS) b
39 on a.distinct_load_ts_all = b.distinct_load_ts;

[Tom]

The INTNX() function does not understand the interval name of "dtyear" that the %SYSFUNC() macro function passed to it.  It was expecting a value like dtyear instead. Remove the quotes from your macro code so that they do not become part of the strings passed to the SAS function.  In macro code you do not need to enclose string literals in quotes because everything is a string to the macro processor.  The macro processor is just looking the & and % characters to know when it should take action and modify your SAS code.

Also you probably will want to attach a FORMAT to your new variable so humans can recognize the number of seconds as an actual datetime value.

, %sysfunc(intnx(dtyear,b.distinct_load_ts,&rtncd.,s)) as aged_out format=datetime19.

Plus did you really want to make variable TODAY_DATE as a character string with single quotes in it? 

Looks you ran

, "'2023-09-05 18:56:07'" as today_date

There are a lot of problems with that.  The name implies that it has a DATE value.  But instead if has a datetime value represent as a string including enclosing quotes.

If you want to make another datetime variable then I would recommend using a different name and using the macro variable to generate a datetime constant. Again if you want the number of seconds to print so humans can understand then attach an appropriate display format.

 ,&today_date.dt as today_datetime format=datetime19.

[Coco_08]

Thankyou for your reply.I am still getting missing values and error on this code.Thankyou for noticing todays_date issue

26 proc sql;
27 create table PRE_QA_PEER as
28 select a.distinct_load_ts_all as distinct_load_ts
29 ,a.volume_records_all as volume_records
30 ,'FCORE_EE.FSC_ENTITY_PEER_GROUP' as table_name
31 ,%sysfunc(intnx(dtyear,b.distinct_load_ts,&rtncd.,s))
SYMBOLGEN: Macro variable RTNCD resolves to 5
ERROR: Argument 2 to function INTNX referenced by the %SYSFUNC or %QSYSFUNC macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
32 as aged_out format datetime19.
33 ,&today_date. as today_date
SYMBOLGEN: Macro variable TODAY_DATE resolves to '2023-09-05 18:56:07'
34 ,case when a.distinct_load_ts_all = b.distinct_load_ts then 'Y'
35 else 'N'
36 end as flag_to_delete from
37 (select distinct_load_ts_all,volume_records_all from VLM_BEFORE_PEER) a
38 left join
39 (select distinct_load_ts from CNT_DLT_VLMS) b
40 on a.distinct_load_ts_all = b.distinct_load_ts;
NOTE: Table WORK.PRE_QA_PEER created, with 903 rows and 6 columns.

[Tom · Accepted solution]

You are using macro code to generate a CONSTANT date.  You are trying base the date off of the constant string b.distinct_load_ts which is not a date.  Instead it is a string you would use as part of an SQL statement to reference a variable from a dataset that you have refered to with the alias B.

If that is what you need to get rid of the macro code and just call the actual SAS function INTNX() directly.  Now it can run for each individual value of b.distinct_load_ts instead of having the macro processor generate a constant date value.  Since you now writing SAS conde instead of using macro code to write the SAS code you need to enclose the string literals in quotes so SAS does not assume you are referring to variables named DTYEAR and S

, intnx('dtyear',b.distinct_load_ts,&rtncd.,'s') as aged_out format=datetime19.

[Kurt_Bremser]

Please READ my post, with diligence. There is no %SYSFUNC in it.

[Coco_08]

Thankyou so much.it works.