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,935

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,935

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
Frequent Contributor
Posts: 78

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?

Posted in reply to TarunKumar

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,935

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 and locked.

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

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