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!
... View more
