Desktop productivity for business analysts and programmers

pulling data with two conditions

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

pulling data with two conditions

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?


Accepted Solutions
Solution
‎07-31-2014 08:49 PM
Respected Advisor
Posts: 3,063

Re: pulling data with two conditions

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

View solution in original post


All Replies
Solution
‎07-31-2014 08:49 PM
Respected Advisor
Posts: 3,063

Re: pulling data with two conditions

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

Trusted Advisor
Posts: 1,203

Re: pulling data with two conditions

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;

Contributor
Posts: 55

Re: pulling data with two conditions

Thanks a lot SASKiwi and stat@sas...

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 329 views
  • 3 likes
  • 3 in conversation