topic Re: pulling data with two conditions in SAS Enterprise Guide

pulling data with two conditions

jimksas — Fri, 01 Aug 2014 00:19:37 GMT

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?

Re: pulling data with two conditions

SASKiwi — Fri, 01 Aug 2014 00:49:11 GMT

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

Re: pulling data with two conditions

stat_sas — Fri, 01 Aug 2014 03:26:12 GMT

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;

Re: pulling data with two conditions

jimksas — Fri, 01 Aug 2014 04:50:06 GMT

Thanks a lot SASKiwi and stat@sas...