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

Posted in reply to sunilreddy

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

Posted in reply to 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;

Super Contributor
Posts: 275

Re: macro datetime variable error

Posted in reply to sunilreddy

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: 7,039

Re: macro datetime variable error

Posted in reply to sunilreddy

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
  • 819 views
  • 0 likes
  • 4 in conversation