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?
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);
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);
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;
Thanks a lot SASKiwi and stat@sas...
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.