Components that are of different data types.

[jorquec]

Hi, 

Sorry for ask again

I had a similar problem that was fixed with this solution in my code( below), however for another table it is not working.

This solution is fine when 

month_id is a VARCHAR(3), however I have a new table where month_id is a integer so I would like to write an arithmetic expression to compare two variables.

When I run the code below it show me this message error

32 GOPTIONS ACCESSIBLE;

33 proc sql;

34 /*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id < &mthid3.;*/

35 delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and input(month_id,best.) <

35 ! (intck('month','01jan1990'd,today())-3);

ERROR: INPUT function requires a character argument.

ERROR: Expression using less than (<) has components that are of different data types.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I understand that I need to change however I have tried some options ,and I am just a beginner user  so sorry for my question.

I tried all this and none is right.

Please could anybody help me?

/*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id < &mthid3.;*/

/*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and input('month_id',best.) < (intck('month','01jan1990'd,today())-3);*/

/*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id<"'(&mthid1.-3)'";*/

delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id<(intck('month','01jan1990'd,today())-3);

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());
%let refdate = &YEARMM1.;/*201906;*/
%let month_id = &MONTHID1.;/*'339';*/
data _null_;
mthid1 = intck('month','01jan1990'd,today());
put mthid1=;
mthid3 = mthid1 - 3;
call symputx('mthid1',mthid1);
call symputx('mthid3',mthid3);
run;
%put mthid1=&mthid1;
%put mthid3=&mthid3;
%put &YEARMM1.;


/*******-----------1.Pay Go Priority Moments ---------*********/
proc sql;

delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and input(month_id,best.) < (intck('month','01jan1990'd,today())-3);

quit;

[Kurt_Bremser]

Just use the numeric column as is:

delete from irm.Consumer_Model_Mart
where internal_CA_model_id =107 and month_id < (intck('month','01jan1990'd,today())-3);

[jorquec]

Hi

I am really struggling with this code, if you could please give an idea how to fix this.

I receive a message error again as below:

33 proc sql;
34 connect to teradata
35 (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
36 execute (
37 /*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id < &mthid3.;*/
38
39 delete from insights_rm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id
39 ! <(intck('month','01jan1990'd,today())-5);
40 ) by teradata;
ERROR: Teradata execute: Syntax error: expected something between '(' and the string 'month'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41 execute (commit;) by teradata;
NOTE: Statement not executed due to NOEXEC option.

%let today=%sysfunc(today());
%let currdt=%sysfunc(datetime());
%let refdate = &YEARMM1.;/*201906;*/
%let month_id = &MONTHID1.;/*'339';*/

data _null_;
mthid1 = intck('month','01jan1990'd,today());
put mthid1=;
mthid3 = mthid1 - 3;
call symputx('mthid1',mthid1);
call symputx('mthid3',mthid3);
run;
%put mthid1=&mthid1;
%put mthid3=&mthid3;
%put &YEARMM1.;
%put &MONTHID1.;
%put &month_id.;

/* delete step*/

proc sql;
connect to teradata
(user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
execute (

delete from insights_rm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id <(intck('month','01jan1990'd,today())-5);
) by teradata;
execute (commit;) by teradata;
quit;

[Kurt_Bremser · Accepted solution]

In explicit passthrough, you need to use the syntax of the DBMS, in this case Teradata. SAS functions will not work there, you need to use the Teradata equivalent.

[jorquec]

Hi, 

Many thanks for your answers and sorry answer so later. 

I used this and it was fine:

proc sql;
connect to teradata
(user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
execute (
DELETE FROM Insights_rm.Consumer_Model_Mart
WHERE  internal_CA_model_id =202 AND 
 
 	month_id <=(SEL B.MODEL_RUN_ID_MAX-3  FROM
			(SELECT  Max(month_id) AS MODEL_RUN_ID_MAX
 			FROM  Insights_rm.Consumer_Model_Mart   A
			WHERE A.internal_CA_model_id =202  )B
			);
			) by teradata;
execute (commit;) by teradata;
quit;

Kindly regards

[jorquec]

Hi

Let me see if I understand you mean that I can not use the 

delete from insights_rm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id <(intck('month','01jan1990'd,today())-5);

 but I could use this instead , as current_date is a teradata funcion

delete from insights_rm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id <( current_date - 150););

 Does it make sense?

[ballardw]

It looks most likely that your MONTH_ID variable is numeric. At least at this call

delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 
      and input(month_id,best.) <.

 (intck('month','01jan1990'd,today())-3);.

So INPUT would not be needed.

Try

delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 
      and month_id < (intck('month','01jan1990'd,today())-3);

but without knowing exactly what is intended that is only a guess.

From your comment in the code

/*delete from irm.Consumer_Model_Mart where internal_CA_model_id =107 and month_id < &mthid3.;*/.

One wonders why you aren't using the commented line.

BTW, did you have code that worked before adding in the macro elements?