BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
atsprink
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

6 REPLIES 6
Reeza
Super User

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
Calcite | Level 5

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_
Rhodochrosite | Level 12

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

atsprink
Calcite | Level 5

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
Calcite | Level 5

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
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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