Help using Base SAS procedures

Is this code logic correct?

Accepted Solution Solved
Reply
Super Contributor
Posts: 338
Accepted Solution

Is this code logic correct?

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


Accepted Solutions
Solution
‎08-26-2013 11:37 AM
Respected Advisor
Posts: 4,649

Re: Is this code logic correct?

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;

PG

PG

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Is this code logic correct?

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;

Respected Advisor
Posts: 4,649

Re: Is this code logic correct?

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.

PG

PG
Contributor
Posts: 74

Re: Is this code logic correct?

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

Super Contributor
Posts: 338

Re: Is this code logic correct?

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

Solution
‎08-26-2013 11:37 AM
Respected Advisor
Posts: 4,649

Re: Is this code logic correct?

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;

PG

PG
Super Contributor
Posts: 338

Re: Is this code logic correct?

Hi PGStats,

Thank you very much for this great help.

Regards

Miris

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 278 views
  • 10 likes
  • 4 in conversation