Hi SAS Forum,
I wanted to pull the records from my warehouse table that are satisfying below criteria:
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
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
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;
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
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')
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
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
Hi PGStats,
Thank you very much for this great help.
Regards
Miris
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.