topic macro datetime variable error in SAS Programming

macro datetime variable error

sunilreddy — Thu, 05 Jun 2014 17:12:52 GMT

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

Re: macro datetime variable error

Anotherdream — Thu, 05 Jun 2014 17:44:04 GMT

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

Re: macro datetime variable error

sunilreddy — Thu, 05 Jun 2014 19:30:02 GMT

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;

Re: macro datetime variable error

slchen — Fri, 06 Jun 2014 13:50:07 GMT

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

Re: macro datetime variable error

Tom — Fri, 06 Jun 2014 14:57:11 GMT

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

...