How do I search by dates?

[a0f6459]

Hi,

I have an ORACLE table that has customer transaction information. I want to be able to only pull data between 2 dates. I don't know even where to begin.

Your help is greatly appreciated.

Thanks

 

[klaz2002]

This is not an Oracle forum. That said, why can't you write a normal SQL query?
ex

select * from
 your_table
 where startdate < transdate < enddate

The startdate or end date should be real dates. If you want this data via the SAS oracle engine, you could select the data via the SQL PASS through facility.

Set your SAS LIBNAME to the oracle database and then select the data that meets your criteria.

 

[a0f6459]

Sorry, I am trying to use either proc sql or a data step.

Which do you recommend?

 

[kdt82]

There's been quite some discussion of efficiency between sql and the datastep - even between where and if subsetting.

The only way to know for sure is to run tests on your data; also consider what you would be more comfortable debugging

Here's a test I ran with 1 million records

data test(drop= i basedate);
basedate = '05jan1996'd;
do i=1 to 1000000;
    dates1 = basedate + ceil(uniform(0)*5);
    dates2 = basedate + ceil(uniform(0)*6);
    dates3 = basedate + ceil(uniform(0)*7);
    output;
end;
run;

proc sql;
create table n as
select * from test where dates1<dates2<dates3;
quit;

data n;
set test;
if dates1<dates2<dates3;
run;

proc sql took and average of 0.34 seconds cpu time and the datastep took 0.26.

So I guess, run both; check the log; decide which you are more comfortable

 

[a0f6459]

I'm still not sure how to run this. Forgive my low level knowledge.

I have an oracle table called activity with customer information. This table has millions and millions of records and I cannot go in and change anything. This table has a transaction_date column in datetime format. I want to just pull data between 2 dates. I can't seem to get this to work. I'm just not understanding the formatting or informatting of data in sas so that I can use dates properly.


proc sql;
create table data as
select * from activity where ????<transaction_date<???;
quit;

 

[MatthiasB]

Is it that you're confused by human readable date information and how to get it into SAS? If so, check this log:

[tt]81 data _null_;
82 myDate = '24dec2005'd;
83 myDateTime = '24dec2005:15:45:20'dt;
84 put 'SAS date is: ' myDate +(-1) ', formatted is: ' myDate date.;
85 put 'SAS datetime is: ' myDateTime +(-1) ', formatted is: ' myDateTime datetime.;
86 run;

SAS date is: 16794, formatted is: 24DEC05
SAS datetime is: 1451058320, formatted is: 24DEC05:15:45:20[/tt]

Depending on how you have the day / month / year information stored, there's also helpful functions like mdy() - just check the SAS manual's date / datetime functions.

 

[ManojAnalytics]

Hi,

You can try this.

proc sql;
create table data as
select * from activity
where transaction_date between date1 and date2
quit;

Thanks

 

[ChrisW75]

Also, there are 2 ways to get the data from Oracle into SAS, depending on how you are set up.
You can set up a libname statement which points to the oracle database so that you can access it exactly as if it was a SAS dataset. Alternatively you can use your proc SQL step to create a link to the Oracle table to query it.
Hopefully there are people around you that you can copy this code from (hardly anyone ever writes these bits from scratch).
If not, let us know, I can probably find some old code somewhere where I used it (I no longer have access to Oracle, so I don't have any recent code on hand).

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.