topic Re: Is this code logic correct? in SAS Procedures

Is this code logic correct?

Mirisage — Mon, 26 Aug 2013 01:53:59 GMT

Hi SAS Forum,

I wanted to pull the records from my warehouse table that are satisfying below criteria:

C_Code = 123 or S_CODE IN ('WANT','NEED')
S_IND = 'N'
OPEN_DATE >= 01MAR2013

This is the code I attempted but doesn't seem pulling correct records that satisfy our conditions above.

proc sql;

create table k as

select

,ACCT_NUM

,OPEN_DATE

,C_Code

,S_CODE

,S_IND

from warehouse_table

where C_Code IN (123) OR S_CODE IN ('WANT','NEED')

and S_IND IN ('N') and oPEN_DATE >= '01MAR2013'd;

quit;

Q: Could some one help me to correct the logic or suggest an alternative code?

Thanks

Mirisage

Re: Is this code logic correct?

Linlin — Mon, 26 Aug 2013 02:56:47 GMT

try:

proc sql;

create table k as

select

ACCT_NUM

,OPEN_DATE

,C_Code

,S_CODE

,S_IND

from warehouse_table

where (C_Code IN (123) OR S_CODE IN ('WANT','NEED'))

and S_IND IN ('N') and oPEN_DATE >= '01MAR2013'd;

quit;

Re: Is this code logic correct?

PGStats — Mon, 26 Aug 2013 03:01:06 GMT

You must add some parenthesis because AND is evaluated before OR :

where (C_Code IN (123) OR S_CODE IN ('WANT','NEED'))

and S_IND IN ('N') and OPEN_DATE >= '01MAR2013'd

Also, make sure that OPEN_DATE is indeed a date and not a datetime value.

Re: Is this code logic correct?

TarunKumar — Mon, 26 Aug 2013 05:40:25 GMT

where (C_Code = 123 OR S_CODE IN ('WANT','NEED'))

and S_IND = 'N' and OPEN_DATE >= '01MAR2013'd

make suer if c_code 123 is single string then use = sing otherwise use c_code in (1,2,3) if c_code is character then use c_code in ('1','2','3')

Re: Is this code logic correct?

Mirisage — Mon, 26 Aug 2013 15:09:00 GMT

Hi Linlin and TarunKumar,
Thank you very much.

Hi PGStats,

Thank you very much. Your comment which says “Also, make sure that OPEN_DATE is indeed a date and not a datetime value” is particularly relevant to my question in hand because OPEN_DATE is a datetime value in my dataset.

Then what I have done was like below:

/*Filtering only records whose value of OPEN_DATE >= '01MAR2013'd */

data t (rename=(OPEN_DATE_1 =OPEN_DATE));

set warehouse_table;

Format OPEN_DATE_1 date9.;

OPEN_DATE_1 = DATEPART (OPEN_DATE);

if OPEN_DATE_1 >= '01MAR2013'd;

drop OPEN_DATE;

run;

/*Pulling the records matching my other conditions*/

proc sql;

create table want as

select

ACCT_NUM

,OPEN_DATE

,C_Code

,S_CODE

,S_IND

from t

where (C_Code IN (123) OR S_CODE IN ('WANT','NEED'))

and S_IND IN ('N') /*and OPEN_DATE >= '01APR2013'd*/;

quit;

/*Q: Could you please see if there is any conflict in my code logic*/

Many thanks for your time and expertise.

Mirisage

Re: Is this code logic correct?

PGStats — Mon, 26 Aug 2013 15:37:25 GMT

Hi Mirisage, your code looks OK but unnecessarily complicated . No need to create an intermediary dataset, you could simply use:

proc sql;

create table k as

select

ACCT_NUM

,datepart(OPEN_DATE) as OPEN_DATE

,C_Code

,S_CODE

,S_IND

from warehouse_table

where

(C_Code IN (123) or S_CODE IN ('WANT','NEED')) and

S_IND IN ('N') and

datepart(OPEN_DATE) >= '01MAR2013'd;

quit;

Re: Is this code logic correct?

Mirisage — Tue, 27 Aug 2013 01:02:16 GMT

Hi PGStats,

Thank you very much for this great help.

Regards

Miris