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
...
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.
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.