topic Re: Convert datetime to char error in New SAS User

Convert datetime to char error

Cello23 — Wed, 20 Mar 2019 09:36:50 GMT

Hi,

i have this macro, it works:

   %macro date_loop(start,end);
   %let start=%sysfunc(inputn(&start,DATETIME16.));
   %let end=%sysfunc(inputn(&end,DATETIME16.));
   %let dif=%sysfunc(intck(HOUR,&start,&end));
     %do i=0 %to &dif;
      %let DATA_INIZIO=%sysfunc(intnx(HOUR,&start,&i,b),DATETIME16.);
	  %let DATA_FINE=%sysfunc(intnx(HOUR,"&DATA_INIZIO"dt,1),DATETIME16.);
      %put &DATA_INIZIO;

	  	  	PROC SQL ; /* ACCODO */ 
			CREATE TABLE  LDS.TORARIO_VUOTO_p  AS 
			
			SELECT 
			KEYATM,
			"&DATA_INIZIO"dt FORMAT=DATETIME16. AS   DATA_INIZIO,
			"&DATA_FINE"dt  FORMAT=DATETIME16. AS   DATA_FINE,

			 datepart("&DATA_INIZIO"dt ) FORMAT=DDMMYY10. AS Date,
			HOUR("&DATA_INIZIO"dt )   FORMAT=BEST12. AS Time,
			WEEKDAY( datepart("&DATA_INIZIO"dt ) ) FORMAT=BEST12. AS Day

			from LDS.TORARIO_ANAG A
			 WHERE KEYATM   = '020081319902752'
			;
			quit;


     %end;
   %mend date_loop;

   %date_loop(01FEB19:00:00:00,28FEB19:23:59:59);

But i need to insert into macro two variables for this value fd = 01FEB19:00:00:00 ld=28FEB19:23:59:59, to determine automatically

first and last hour of month:

data _null_;
dt = '01FEB2019:00:00:00'dt; 
fd = intnx('dtmonth', dt, 0, 'B');
ld = intnx('dtmonth', dt, 0, 'E');

fd= put(fd,datetime.);
ld= put(Ld,datetime.);
run;

%put NOTE: &fd &ld &dt;

i have this error, can you tell me what the error is in the format? thank's :

  data _null_;
197      dt = '01FEB2019:00:00:00'dt;
198      fd = intnx('dtmonth', dt, 0, 'B');
199      ld  = intnx('dtmonth', dt, 0, 'E');
200
201      fd= put(fd,datetime.);
202      ld= put(Ld,datetime.);
203  run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      201:9   202:9
NOTE: Invalid numeric data, '01FEB19:00:00:00' , at line 201 column 9.
NOTE: Invalid numeric data, '28FEB19:23:59:59' , at line 202 column 9.
dt=1864598400 fd=. ld=. _ERROR_=1 _N_=1
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


204
205  %put NOTE: &fd &ld  &dt;
NOTE:            .            .    1861920000

Re: Convert datetime to char error

Ksharp — Wed, 20 Mar 2019 10:54:11 GMT

You override variables fd,ld .

data _null_;
dt = '01FEB2019:00:00:00'dt; 
fd = intnx('dtmonth', dt, 0, 'B');
ld = intnx('dtmonth', dt, 0, 'E');

new_fd= put(fd,datetime.);
new_ld= put(Ld,datetime.);
run;

Re: Convert datetime to char error

Cello23 — Wed, 20 Mar 2019 13:30:46 GMT

i tried but i have an error:

data _null_;
    fd = intnx('dtmonth', &dt, 0, 'B');
    ld  = intnx('dtmonth', &dt, 0, 'E');
 
	fd_new= put(fd,datetime.);
	ld_new= put(Ld,datetime.);
	put fd_new d  ld_new;
run;

%put NOTE: &fd_new;
 
 
 
   %macro date_loop(start,end);
   %let start=%sysfunc(inputn(&start,DATETIME16.));
   %let end=%sysfunc(inputn(&end,DATETIME16.));
   %let dif=%sysfunc(intck(HOUR,&start,&end));
     %do i=0 %to &dif;
      %let DATA_INIZIO=%sysfunc(intnx(HOUR,&start,&i,b),DATETIME16.);
	  %let DATA_FINE=%sysfunc(intnx(HOUR,"&DATA_INIZIO"dt,1),DATETIME16.);
      %put &DATA_INIZIO;

	  	  	PROC SQL ; /* ACCODO */ 
			CREATE TABLE  LDS.TORARIO_VUOTO_p  AS 
			
			SELECT 
			KEYATM,
			"&DATA_INIZIO"dt FORMAT=DATETIME16. AS   DATA_INIZIO,
			"&DATA_FINE"dt  FORMAT=DATETIME16. AS   DATA_FINE,

			 datepart("&DATA_INIZIO"dt ) FORMAT=DDMMYY10. AS Date,
			HOUR("&DATA_INIZIO"dt )   FORMAT=BEST12. AS Time,
			WEEKDAY( datepart("&DATA_INIZIO"dt ) ) FORMAT=BEST12. AS Day

			from LDS.TORARIO_ANAG A
			 WHERE KEYATM   = '020081319902752'
			;
			quit;


     %end;
   %mend date_loop;

   %date_loop(&fd_new,&ld_new);

Log error:

276  data _null_;
277      fd = intnx('dtmonth', &dt, 0, 'B');
278      ld  = intnx('dtmonth', &dt, 0, 'E');
279
280      fd_new= put(fd,datetime.);
281      ld_new= put(Ld,datetime.);
282      put fd_new d  ld_new;
283  run;

NOTE: Variable d is uninitialized.
01JAN19:00:00:00 . 31JAN19:23:59:59
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


WARNING: Apparent symbolic reference FD_NEW not resolved.
284
285  %put NOTE: &fd_new;
NOTE: &fd_new
286
287
288
289     %macro date_loop(start,end);
290     %let start=%sysfunc(inputn(&start,DATETIME16.));
291     %let end=%sysfunc(inputn(&end,DATETIME16.));
292     %let dif=%sysfunc(intck(HOUR,&start,&end));
293       %do i=0 %to &dif;
294        %let DATA_INIZIO=%sysfunc(intnx(HOUR,&start,&i,b),DATETIME16.);
295        %let DATA_FINE=%sysfunc(intnx(HOUR,"&DATA_INIZIO"dt,1),DATETIME16.);
296        %put &DATA_INIZIO;
297
298              PROC SQL ; /* ACCODO */
299              CREATE TABLE  LDS.TORARIO_VUOTO_p  AS
300
301              SELECT
302              KEYATM,
303              "&DATA_INIZIO"dt FORMAT=DATETIME16. AS   DATA_INIZIO,
304              "&DATA_FINE"dt  FORMAT=DATETIME16. AS   DATA_FINE,
305
306               datepart("&DATA_INIZIO"dt ) FORMAT=DDMMYY10. AS Date,
307              HOUR("&DATA_INIZIO"dt )   FORMAT=BEST12. AS Time,
308              WEEKDAY( datepart("&DATA_INIZIO"dt ) ) FORMAT=BEST12. AS Day
309
310              from LDS.TORARIO_ANAG A
311               WHERE KEYATM   = '020081319902752'
312              ;
313              quit;
314
315
316       %end;
317     %mend date_loop;
318
319     %date_loop(&fd_new,&ld_new);
WARNING: Apparent symbolic reference FD_NEW not resolved.
WARNING: Apparent symbolic reference LD_NEW not resolved.
WARNING: Apparent symbolic reference FD_NEW not resolved.
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is
         out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The
      result of the operations have been set to a missing value.
WARNING: Apparent symbolic reference LD_NEW not resolved.
WARNING: Argument 1 to function INPUTN referenced by the %SYSFUNC or %QSYSFUNC macro function is
         out of range.
NOTE: Mathematical operations could not be performed during %SYSFUNC function execution. The
      result of the operations have been set to a missing value.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
       operand is required. The condition was: &dif
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro DATE_LOOP will stop executing.

thank's

Re: Convert datetime to char error

Tom — Wed, 20 Mar 2019 13:36:22 GMT

You are trying to reference a macro variable you never created.

You can use CALL SYMPUTX() to create a macro variable in a data step.

Re: Convert datetime to char error

Cello23 — Wed, 20 Mar 2019 13:44:25 GMT

i tried:

 		data _null_;
		call symput('dt', '01JAN2019:00:00:00'dt );
		run;

  		data _null_;
		 call symput('FD', intnx('dtmonth', &dt, 0, 'B') );
		 call symput('LD', intnx('dtmonth', &dt, 0, 'E') );
  		run;

  		data _null_;
		 call symput('FD_new', put(datepart(&fd),datetime.) );
		 call symput('LD_new', put(datepart(&ld),datetime.) );
  		run;

i have this error:

336
337
338          data _null_;
339          call symput('dt', '01JAN2019:00:00:00'dt );
340          run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      339:27
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


341
342          data _null_;
343           call symput('FD', intnx('dtmonth', &dt, 0, 'B') );
344           call symput('LD', intnx('dtmonth', &dt, 0, 'E') );
345          run;

NOTE: Numeric values have been converted to character values at the places given by:
      (Line):(Column).
      343:28   344:28
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


346
347          data _null_;
348           call symput('FD_new', put(datepart(&fd),datetime.) );
349           call symput('LD_new', put(datepart(&ld),datetime.) );
350          run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

thank's!!

Re: Convert datetime to char error

Tom — Wed, 20 Mar 2019 14:06:02 GMT

Why are you using the older call symput() function instead of the newer call symputx()? Only use the older version if you need to have leading or trailing spaces written into the macro variable.

What are you going to use these macro variables to do?

If you are just going to use them to assign values or compare values there is no need to apply any format to them.

202   data _null_;
203     call symputx('today',date());
204     call symputx('now',datetime());
205   run;

NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.01 seconds


206   %put &=today &=now;
TODAY=21628 NOW=1868695382.4
207
208   data _null_;
209     if date()=&today then put 'It is today.';
210     if abs(datetime()-&now) < 60*60 then put 'It is within an hour of now.';
211   run;

It is today.
It is within an hour of now.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

If you do want to store the formatted value (a string instead of a number) into the macro variable then convert using the format. Make sure to use a format that is long enough to store the full four digits of the year. But then if you want to use those values in your code you will need add quotes and suffix make valid date or datetime literal values.

212   data _null_;
213     call symputx('today',put(date(),date9.));
214     call symputx('now',put(datetime(),datetime20.));
215   run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


216   %put &=today &=now;
TODAY=20MAR2019 NOW=20MAR2019:10:05:05
217
218   data _null_;
219     if date()="&today"d then put 'It is today.';
220     if abs(datetime()-"&now"dt) < 60*60 then put 'It is within an hour of now.';
221   run;

It is today.
It is within an hour of now.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.01 seconds

Re: Convert datetime to char error

Kurt_Bremser — Wed, 20 Mar 2019 14:08:38 GMT

These are not ERRORs, these are NOTEs that inform you about implicit type conversions.

By using call symputx() instead of call symput(), you can suppress those NOTEs:

data _null_;
call symputx('dt','01JAN2019:00:00:00'dt);
run;

data _null_;
call symputx('FD',intnx('dtmonth',&dt,0,'B'));
call symputx('LD',intnx('dtmonth',&dt,0,'E'));
run;

data _null_;
call symputx('FD_new',put(datepart(&fd),datetime.));
call symputx('LD_new',put(datepart(&ld),datetime.));
run;

%put fd_new=&fd_new.;
%put ld_new=&ld_new.;

Log:

27         data _null_;
28         call symputx('dt','01JAN2019:00:00:00'dt);
29         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

30         
31         data _null_;
32         call symputx('FD',intnx('dtmonth',&dt,0,'B'));
33         call symputx('LD',intnx('dtmonth',&dt,0,'E'));
34         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

35         
36         data _null_;
37         call symputx('FD_new',put(datepart(&fd),datetime.));
38         call symputx('LD_new',put(datepart(&ld),datetime.));
39         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

2                                                          Das SAS System                            07:11 Wednesday, March 20, 2019

40         
41         %put fd_new=&fd_new.;
fd_new=01JAN60:05:59:10
42         %put ld_new=&ld_new.;
ld_new=01JAN60:05:59:40

Re: Convert datetime to char error

Cello23 — Wed, 20 Mar 2019 14:50:28 GMT

Using the modification you suggested, the result is not right, fd_new = 01JAN60:05:59:10 and ld_new =01JAN60:05:59:4 :

496
497 %put fd_new=&fd_new.;
fd_new=01JAN60:05:59:10
498 %put ld_new=&ld_new.;
ld_new=01JAN60:05:59:4

The result should be fd_new = 01JAN19:00:00:00 (begin of month) and ld_new =31JAN19:23:59:59 (end of month)

thank's

Re: Convert datetime to char error

Tom — Wed, 20 Mar 2019 15:09:32 GMT

It sounds like your question is just how to generate macro variables with strings that LOOK LIKE datatime values?

It is very easy to do.

First you need to start with a value that is a valid datetime values.

%let dt = '01FEB2019:00:00:00'dt;

Then you can use INTNX() function. Make sure to use a width on the format that is long enough that SAS will include 4 digits for the year. (Remember Y2K?)

%let fd=%sysfunc(intnx(dtmonth,&dt,0,b),datetime20);
%let ld=%sysfunc(intnx(dtmonth,&dt,0,e),datetime20);

Results:

251   %put &=dt;
DT='01FEB2019:00:00:00'dt
252   %put &=fd;
FD=01FEB2019:00:00:00
253   %put &=ld;
LD=28FEB2019:23:59:59

Notice how the new macro variables look different from the first one?

If you want to use those values as datetime literals in your code then make sure to add the quotes and dt suffix.

%let fd=%sysfunc(intnx(dtmonth,&dt,0,b),datetime20);
%let fd="&fd"dt;
%let ld=%sysfunc(intnx(dtmonth,&dt,0,e),datetime20);
%let ld="&ld"dt;

Results:

244   %put &=dt;
DT='01FEB2019:00:00:00'dt
245   %put &=fd;
FD="01FEB2019:00:00:00"dt
246   %put &=ld;
LD="28FEB2019:23:59:59"dt

Re: Convert datetime to char error

Kurt_Bremser — Wed, 20 Mar 2019 15:13:22 GMT

I mistakenly copied your wrong datepart() calls.

Simplify the code to

%let dt='01JAN2019:00:00:00'dt;

data _null_;
call symputx('FD',put(intnx('dtmonth',&dt,0,'B'),datetime19.));
call symputx('LD',put(intnx('dtmonth',&dt,0,'E'),datetime19.));
run;

%put fd=&fd.;
%put ld=&ld.;

Log:

27         %let dt='01JAN2019:00:00:00'dt;
28         
29         data _null_;
30         call symputx('FD',put(intnx('dtmonth',&dt,0,'B'),datetime19.));
31         call symputx('LD',put(intnx('dtmonth',&dt,0,'E'),datetime19.));
32         run;

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

33         
34         %put fd=&fd.;
fd=01JAN2019:00:00:00
35         %put ld=&ld.;
ld=31JAN2019:23:59:59

Re: Convert datetime to char error

Cello23 — Mon, 25 Mar 2019 09:22:20 GMT

Tom and KurtBremser , your answers will solve my problem, thank's so much!!