pulling data with two conditions

[jimksas]

please help -

i pulling data from oracle table with two condition

- date and card_nbr

oracle table looks like this;

date            card_nbr

09Feb14      1234

09Feb15      5678

10Feb14      9875

09Feb14      1934

09Feb15       1678

11Feb14       0875

i am trying to run code like this to pull some specific in between date data

options mprint mlogic symbolgen;

data _null_;

tempdate_1=intnx('day',today(),-172);

tempdate_2=intnx('day',today(),-170);

call symput('date1',put(tempdate_1,yymmdd10.));

call symput('date2',put(tempdate_2,yymmdd10.));

run;

/*oracle connection detail goes here*/

data test;

set oracle.table;

if date (between &date1 and &date2) and card_nbr in (0875 1234 5678);

run;

/*it is giving me 0 records even after date1 resolve to 2014-02-09 and date2 resolves to 2014-02-11*/

is my syntax is wrong?

[SASKiwi · Accepted solution]

You are using a SAS DATA step to read from Oracle so you must use SAS date conventions.

Try: where date between "09feb2014"d and "11feb2014"d and card_nbr in (0875 1234 5678);

Also check the column attributes of date - is it a date or a datetime?

If it is a datetime try: where date between "09feb2014:00:00:00"dt and "11feb2014:00:00:00"dt and card_nbr in (0875 1234 5678);

[stat_sas]

There are couple of things need to be done (highlighted) to fix this.

data _null_;
tempdate_1=intnx('day',today(),-172);
tempdate_2=intnx('day',today(),-170);
call symput('date1',put(tempdate_1,date7.));
call symput('date2',put(tempdate_2,date7.));
run;

data test;
set oracle.table;

if date>="&date1"d and date<="&date2"d and card_nbr in (0875 1234 5678);
run;

[jimksas]

Thanks a lot SASKiwi and stat@sas...