macro datetime variable error

[sunilreddy]

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

[Anotherdream]

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

[sunilreddy]

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;

[slchen]

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

[Tom]

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

...