topic Components that are of different data types. in New SAS User

Components that are of different data types.

jorquec — Wed, 21 Aug 2019 14:28:27 GMT

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;

Re: Components that are of different data types.

Kurt_Bremser — Wed, 21 Aug 2019 14:38:15 GMT

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

Re: Components that are of different data types.

ballardw — Wed, 21 Aug 2019 14:44:20 GMT

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?

Re: Components that are of different data types.

jorquec — Thu, 22 Aug 2019 19:33:11 GMT

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;

Re: Components that are of different data types.

Kurt_Bremser — Thu, 22 Aug 2019 20:56:52 GMT

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.

Re: Components that are of different data types.

jorquec — Thu, 22 Aug 2019 21:32:53 GMT

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?

Re: Components that are of different data types.

jorquec — Tue, 27 Aug 2019 07:31:30 GMT

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