topic Re: Proc SQL SELECT issue in SAS Programming

Proc SQL SELECT issue

atsprink — Sat, 10 Feb 2018 05:03:36 GMT

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.

Re: Proc SQL SELECT issue

Reeza — Sat, 10 Feb 2018 05:28:38 GMT

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.

Re: Proc SQL SELECT issue

atsprink — Sat, 10 Feb 2018 16:48:16 GMT

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

Re: Proc SQL SELECT issue

kiranv_ — Sat, 10 Feb 2018 17:23:13 GMT

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

Re: Proc SQL SELECT issue

novinosrin — Sat, 10 Feb 2018 17:23:11 GMT

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

Re: Proc SQL SELECT issue

atsprink — Sat, 10 Feb 2018 17:34:07 GMT

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?

Re: Proc SQL SELECT issue

atsprink — Sat, 10 Feb 2018 17:36:27 GMT

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.