Help Comparing Datetime Macro Variables

[elwayfan446]

Hello.

I have looked all over the internet and can't really find a solution to a problem I am having.  Hoping someone can help me soon.

I am trying to update a datetime field from a transactional dataset to a datetime field in a master dataset.  To get the particular datetimes that I need, I am running a query to select a datetime into a variable to call when comparing.  Let me see if I can step you through my process.

First I am trying to set the datetime variables by selecting from other datasets:

proc sql;
select 
max(grid_id) as grid_id 
into :stage_grid_id 
from msrflow.msrflow_srp_adj_base_stage s
;
quit;

%put &stage_grid_id;


proc sql;
select 
max(grid_id) as grid_id 
into :base_grid_id
from msrflow.msrflow_srp_adjusters_base 
;
quit;

%put &base_grid_id;

As you can see, I am creating a stage grid ID (which is a datetime) and a base grid ID (which is a datetime).  If I dont format, it shows up as some sort of numerical value.

If I format as a datetime21.2 before I insert into the variable, it looks correct.

Next, I try to do the compare so I can update rows in the dataset to the top variable you see in the screenshots to the bottom variable. 

proc sql;
update msrflow.msrflow_srp_adjusters_base as b
set
	END_GRID_DATE = &stage_grid_id 
where end_grid_date is null and &stage_grid_id > &base_grid_id
;
quit;

When I run this code, I get a syntax error.

If I don't format it, it will run properly but must read the datetime wrong because it inserts the incorrect datetime in the master dataset. 

The highlighted datetimes are what inserted.  The time that was inserted was 11:13:20 PM.  However, as you can see from the screenshot above, it should have been 9:05:56 AM.  I have no idea why it is doing this.

My primary goal is to be able to compare datetime stamps in order to update this particular dataset and then insert new rows.  I am hoping you can see if I am missing something or suggest a better way to do this.

Thanks!

[Tom · Accepted solution]

NOTE: inserting photographs of your data into your post instead of the actual text makes it very hard to read.  

It looks like you are not formatting the number of seconds properly when generating the macro variable.

If you don't tell PROC SQL how to format the value it will use BEST8. which is not longer enough to exactly represent the number of seconds you need for your datetime values.

484   proc sql noprint;
485   select datetime()
486        , datetime() format=32.
487        , datetime() format=datetime20.
488     into :noformat trimmed
489        , :formatted trimmed
490        , :datetimestr trimmed
491     from sashelp.class(obs=1)
492   ;
493   %put &=noformat &=formatted &=datetimestr;
NOFORMAT=1.8729E9 FORMATTED=1872947004 DATETIMESTR=08MAY2019:15:03:24

If your macro variable has a value like 1872947004 then you use it as it is.

But if your macro variable has a value like 08MAY2019:15:03:24 then you will need to convert it into a datetime literal (constant) by adding quotes and DT suffix.

%put DateTime value = "&datetimestr"dt ;
DateTime value = "08MAY2019:15:05:56"dt

[elwayfan446]

@Tom , this worked well.  I am happy that it was such an easy fix.  Thank you for the help!