Aritmetic with Data and String issue

[jorquec]

Hi, 

I am having issues to compare two columns  one of them is  called mthid3 and another month_id . 

Month_id is a Varchar  ( e.g  353, 354, 355 .....) and I am not allowed to change the format so I would like to write code below :

I have tried this but doesn't work:

proc sql;
delete from ismecorp.SMB_Model_Mart where model_id ='128' and month_id< &mthid3.;
run;

Which is the correct  way to write this code?

Example A:

proc sql;
delete from ismecorp.SMB_Model_Mart where model_id ='128' and month_id<' &mthid3.';
run;

Example B:

proc sql;
delete from ismecorp.SMB_Model_Mart where model_id ='128' and month_id<" ' (&mthid3.)' ";
run;

None of this???

Many thanks for some help.

%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.;

proc sql;
delete from ismecorp.SMB_Model_Mart where model_id ='128' and month_id< &mthid3.;
run;

[Jagadishkatam]

Please try the below untested code

proc sql;
delete from ismecorp.SMB_Model_Mart where model_id ='128' and input(month_id,best.) < (intck('month','01jan1990'd,today())-3);
run;

[jorquec]

Hi

Sorry but it seems is not working as well, it has been running for hours and nothing happens .

[PaigeMiller]

---

@jorquec wrote:

Hi

Sorry but it seems is not working as well, it has been running for hours and nothing happens .

---

We don't know what this means. Why (explain in detail) do you say "nothing happens"? Show us the exact code you are using by clicking on the "running man" icon and pasting the code into the window that appears.

(By the way, the last line of the code from @Jagadishkatam says RUN; but it should really say QUIT;    )

[Jagadishkatam · Accepted solution]

Thank you @PaigeMiller, I agree it should be quit

[Jagadishkatam]

did you observe any error in the log

[jorquec]

Sorry guys, no log errors yet , probably is our server that has a problem today.

I am going to fix RUN for QUIT and try again, I will let you know if it is fine tomorrow.

Many thanks.

[PaigeMiller]

@jorquec 

If the code uses RUN; instead of QUIT; then it can appear as if PROC SQL is running for hours (well actually, forever) but the work is of PROC SQL is probably done. Can you submit the command QUIT; and see if anything happens?

[jorquec]

Hi

I made the change RUn for QUIT and it is fine now run in just few seconds . As you told the job was done but never appear as end.

Many thanks.

[jorquec]

Hi, 

Sorry for as again about a similar problem but I have tried today in another table and I received a error message as below:

Could you please tell me if there is another way to write , month_id is a integer like 351, 352... in this table.

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.

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

quit;