I've PROCESSED_DTTM variable(informat=datetime19., format=datetime19.) in TEST dataset
%macro delbkp;
Proc sql;
select count(distinct processed_dttm), min(processed_dttm)
into :cnt_dttm, :min_dttm
from TEST
;
Quit;
%put &cnt_dttm &min_dttm ;
%IF &cnt_dttm > 3 %then %do;
proc sql;
delete * from TEST
where processed_dttm="&min_dttm"dt
;
Quit;
%end;
%mend; %delbkp;
ERROR: Invalid date/time/datetime constant "1.7176E9"dt.
MPRINT(DELBKP): delete * from TEST where processed_dttm="1.7176E9"dt ;
Can u suggest me to throw out this error
Sas doesn't store your informat or your format to a macro variable.. So when you call &min_dttm you should notice a very long number and not any kind of date format.
the Dt operator is when you have a date string, something like "10Mar2009:00:00:00.000"dt.
simply remove the quote and the dt, and your process should work.
aka have
proc sql;
delete * from TEST
where processed_dttm=&min_dttm
I've tried it earlier, its not throwing any syntax error, but still records are not deleting from TEST table.
15 | |
16 | Proc sql; |
17 | select count(distinct processed_dttm), min(processed_dttm) |
18 | into :cnt_dttm, :min_dttm |
19 | from TEST |
20 | ; |
21 | Quit; |
NOTE: PROCEDURE SQL used (Total process time):
real time | 17.72 seconds | |
cpu time | 0.14 seconds | |
22 | %put &cnt_dttm &min_dttm ; |
2 1.7176E9
23 | |
24 | Options mprint mlogic; |
25 | %macro delbkp; |
26 | |
27 | |
28 | %IF &cnt_dttm > 0 %then %do; |
29 | proc sql; |
30 | delete * from TEST |
31 | where processed_dttm=&min_dttm |
32 | ; |
33 | Quit; |
34 | %end; |
35 | %mend; %delbkp; |
MLOGIC(DELBKP): Beginning execution.
MLOGIC(DELBKP): %IF condition &cnt_dttm > 0 is TRUE
MPRINT(DELBKP): proc sql;
MPRINT(DELBKP): delete * from TEST where processed_dttm=1.7176E9 ;
NOTE: No rows were deleted from TEST.
MPRINT(DELBKP): Quit;
The reason, I doubt, is because SAS store inaccurate number of datetime in macro variable. From example below, you could see &min resolve to 1.6098E9, put it into datetime format, it become to: 04JAN2011:22:40:00, not 05Jan2011:11:34:11, so you could not delete row. Do I miss thing here?
data have;
input date datetime19.;
format date datetime19.;
cards;
01Jul2013:05:22:14
23Aug2012:06:11:12
05Jan2011:11:34:11
;
run;
proc sql;
select count(distinct date),min(date)into:num,:min from have; quit;
%put &num &min %sysfunc(putn(&min,datetime19.));
SAS is using BEST12. to format the result of the MIN() aggregate function. Tell it to use a different format.
You could use F14. (that is long enough to hold the current time) or F15. to leave a little room for future times.
select count(distinct processed_dttm)
, min(processed_dttm) format=F15.
into :cnt_dttm
, :min_dttm
from TEST
;
...
where processed_dttm=&min_dttm
...
Or you could use DATETIME19. and then use "&min_dttm"dt to reference the result as a datetime literal.
select count(distinct processed_dttm)
, min(processed_dttm) format=datetime19.
into :cnt_dttm
, :min_dttm
from TEST
;
...
where processed_dttm="&min_dttm"dt
...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.