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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.