DATA Step, Macro, Functions and more

macro datetime variable error

Reply
Frequent Contributor
Posts: 115

macro datetime variable error

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

Super Contributor
Posts: 418

Re: macro datetime variable 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

Frequent Contributor
Posts: 115

Re: macro datetime variable error

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;

Super Contributor
Posts: 275

Re: macro datetime variable error

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

Super User
Super User
Posts: 6,500

Re: macro datetime variable error

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

...


Ask a Question
Discussion stats
  • 4 replies
  • 727 views
  • 0 likes
  • 4 in conversation