DATA Step, Macro, Functions and more

Proc SQL SELECT issue

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Proc SQL SELECT issue

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.


Accepted Solutions
Solution
‎02-10-2018 12:40 PM
PROC Star
Posts: 509

Re: Proc SQL SELECT issue

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 23,700

Re: Proc SQL SELECT issue

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.


 

Occasional Contributor
Posts: 7

Re: Proc SQL SELECT issue

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

 

 

 

 

Solution
‎02-10-2018 12:40 PM
PROC Star
Posts: 509

Re: Proc SQL SELECT issue

[ Edited ]

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

Occasional Contributor
Posts: 7

Re: Proc SQL SELECT issue

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?

Occasional Contributor
Posts: 7

Re: Proc SQL SELECT issue

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.

PROC Star
Posts: 1,792

Re: Proc SQL SELECT issue

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 282 views
  • 0 likes
  • 4 in conversation