BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Coco_08
Calcite | Level 5

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

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.

Coco_08
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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.

 

Coco_08
Calcite | Level 5

Thankyou so much.it works.

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
  • 8 replies
  • 1201 views
  • 5 likes
  • 4 in conversation