Proc SQL SELECT issue

[atsprink]

Hello all,

I am having an issue with a proc sql command.  

proc sql;
	select price from sasuser.ERCOT
	where Date >= '01Jun1700:00:00'd and Date <= '31Dec1723:00:00'd;
quit;

As you can see from the code I am just trying to grab prices from a certain date-time range.I have attached the csv file I used to import the data.  I don't receive any errors but the log does say that 0 rows were selected.  I am not sure what I am missing here as this is a pretty straight forward sql query.  SAS documentation not helping me here unfortunately.

[Reeza]

Those are date times they need a DT after the quotes not a D. 

DT -> DateTime

D -> Date

---

@atsprink wrote:

Hello all,

I am having an issue with a proc sql command.  

proc sql;
	select price from sasuser.ERCOT
	where Date >= '01Jun1700:00:00'd and Date <= '31Dec1723:00:00'd;
quit;

As you can see from the code I am just trying to grab prices from a certain date-time range.I have attached the csv file I used to import the data.  I don't receive any errors but the log does say that 0 rows were selected.  I am not sure what I am missing here as this is a pretty straight forward sql query.  SAS documentation not helping me here unfortunately.

---

[atsprink]

Hi Reeza,

I tried your solution and it still says 0 rows were selected.  I realized I had the wrong date time in the original question so it has been updated here.  Here is what my log shows me after running the script.

77
78 * QUESTION 2;
79 * filter data first to june-september;
80 proc sql;
81 select Price from sasuser.ERCOT
82 where Date >= '01JUN1700:00:00'dt and Date <= '30SEP1723:00:00'dt;
NOTE: No rows were selected.
83 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

[kiranv_ · Accepted solution]

that is because no rows meet your where condition and your year 1700 and 1723 looks kind of odd to me.

[atsprink]

Hi kiranv_,

So I think I may have found the issue given your reply.  The datetime should be read as 01Jun17 00:00:00 through 30Sep17 23:00:00, so June 1 2017 at 12:00 am through Sept 30 11:00 pm.  Should I separate the year and time within the single quotes?

[atsprink]

Yep as soon as I space changed the datetime to '01Jun17 00:00:00' it worked.  I forgot to put a space between the year and hour.  Thank you for your comment about the time looking weird.

[novinosrin]

your date values in CSV file are not quite sas datetime values for sas to recognize. You can read the first value just as a date value and still filter with sas date constant 

1/1/2017 0:00 23.3575 LZ_AEN   

1/1/2017 can be read as a sas date value and then you could filter with a whatever sas date constant