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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

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;

PGStats
Opal | Level 21

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
TarunKumar
Pyrite | Level 9

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

Mirisage
Obsidian | Level 7

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

PGStats
Opal | Level 21

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
Mirisage
Obsidian | Level 7

Hi PGStats,

Thank you very much for this great help.

Regards

Miris

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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