BookmarkSubscribeRSS Feed
sunilreddy
Fluorite | Level 6

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

4 REPLIES 4
Anotherdream
Quartz | Level 8

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
Fluorite | Level 6

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
Lapis Lazuli | Level 10

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
Super User Tom
Super User

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

...


sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1972 views
  • 0 likes
  • 4 in conversation