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

Hello, I'm having an issue with the dates in my logic, where they both appear to be the same date type.  My code below is the full code as of now - and please let me know if you need additional information in order to help..  Many Thanks.

 

 

 

proc sql;

create table work.Priest_srvcptidrdailypeak_arch as

select DATEPART(operatingdate) format ddmmyy10. as OperatingDate , uidservicepoint,

h1, h2, h3, h4, h5, h6, h7, h8, h9, h10, h11, h12, h13, h14, h15, h16,

h17, h18, h19, h20, h21, h22, h23, h24

from hud.srvcptidrdailypeak_arch

where operatingdate between "01JUN2016"D and "02JUN2016"D ;

quit;

proc sql;

create table work.Priest_lsservicepoint as

select uidservicepoint, juriscode

from hud.lsservicepoint;

quit;

 

proc sql;

create table work.PRIEST_Merge1 as

select A.*, b.juriscode

from work.Priest_srvcptidrdailypeak_arch as A left join work.Priest_lsservicepoint as b

on a.UIDSERVICEPOINT=b.UIDSERVICEPOINT;

quit;

proc sort data=work.PRIEST_Merge1 NODUP; by _all_ ; run;

proc sql;

create table work.Priest_srvcpthist as

select uidservicepoint, uiddp, uiddlc, nem, uidratefactor, starttime, stoptime

from hud.srvcpthist

where uidratefactor in (237,238) and uiddlc = 1 and nem is null or 'N'

and uiddp not in (1,6,13,5,26,7,8,12,14,10,9,11) ;

quit;

proc sql;

create table work.PRIEST_Merge2 as

select A.*, b.uiddp, b.uiddlc, b.nem, b.uidratefactor, b.starttime, b.stoptime

from work.PRIEST_Merge1 as A left join work.Priest_srvcpthist as b

on a.UIDSERVICEPOINT=b.UIDSERVICEPOINT

where OperatingDate between starttime and coalesce(stoptime,"01JAN4000");

quit;

proc sort data=work.PRIEST_Merge2 NODUP; by _all_ ; run;

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

I haven't reviewed your code but, in your last proc sql call, you didn't define your date constant as a date. i.e., you used:

coalesce(stoptime,"01JAN4000")

rather than

coalesce(stoptime,"01JAN4000"d)

Art, CEO, AnalystFinder.com

View solution in original post

3 REPLIES 3
SuryaKiran
Meteorite | Level 14

Hi,

 

From where are you getting the Starttime and stoptime.

where OperatingDate between starttime and coalesce(stoptime,"01JAN4000");

 

This where clause is not valid. If your trying to take the starttime and stoptime from a table, than use a data _Null_ and CALL SYMPUT to put your values in a macro and later reference them in the where clause.

 

Thanks,

Suryakiran

Thanks,
Suryakiran
art297
Opal | Level 21

I haven't reviewed your code but, in your last proc sql call, you didn't define your date constant as a date. i.e., you used:

coalesce(stoptime,"01JAN4000")

rather than

coalesce(stoptime,"01JAN4000"d)

Art, CEO, AnalystFinder.com

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
  • 3 replies
  • 2016 views
  • 1 like
  • 3 in conversation