BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jorquec
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16
Thank you @PaigeMiller, I agree it should be quit
Thanks,
Jag

View solution in original post

9 REPLIES 9
Jagadishkatam
Amethyst | Level 16

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;
Thanks,
Jag
jorquec
Quartz | Level 8

Hi

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Jagadishkatam
Amethyst | Level 16
Thank you @PaigeMiller, I agree it should be quit
Thanks,
Jag
Jagadishkatam
Amethyst | Level 16

did you observe any error in the log

Thanks,
Jag
jorquec
Quartz | Level 8

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
Diamond | Level 26

@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?

--
Paige Miller
jorquec
Quartz | Level 8

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
Quartz | Level 8

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;

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 875 views
  • 0 likes
  • 3 in conversation