topic Re: unable to store datetime value in a macro variable in SAS Procedures

unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 16:23:10 GMT

I'm trying to store the datetime() value into a macro variable for use within a little program: (example with the baseball table of the VMware student edition )

proc sql;
create table work.start_falnr as
select max(nhits) as max_hits,
round(datetime()) as process_start_integer,
put(datetime(),is8601dt.) as tstamp,
datetime() as process_start
from sashelp.baseball;
quit;

proc sql noprint;
select max_hits,process_start_integer,process_start,tstamp into
:max_hits,:process1,:process2,:tstamp
from work.start_falnr;
quit;

proc sql;
create table work.output as
select process_start_integer,&max_hits,&process1,&process2,&tstamp from work.start_falnr;
quit;

The actual exact value of process_start_integer is 1734088402., but the values written to the output table are 1734100000 and 1734100000 resp. for process1 and process2

Why can I not store the exact value this way?

The tstamp value (put(datetime(),is8601dt.) ) yields an error message in the last proc sql ...

Re: unable to store datetime value in a macro variable

Hercules — Sat, 13 Dec 2014 17:34:01 GMT

Use:

%let datetime_var = "%sysfunc(datetime(),datetime19.)"dt

then use this anywhere as an macro variable @datetime_var

Hope this help.

Re: unable to store datetime value in a macro variable

Tom — Sat, 13 Dec 2014 18:11:32 GMT

Since you are calling DATETIME() function three times you could possibly get three different times depending on when the function calls actually run.

If you just want one value then call the function only once. Why not just call it using macro code instead of PROC SQL?

%let start_time = %sysfunc(datetime());

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:23:20 GMT

using @datetime_var yields an error message:

ERROR 200-322: The symbol is not recognized and will be ignored

The code I'm using is not wrapped up within a macro, perhaps I need to wrap it up in there?

put(datetime(),is8601dt.) as tstamp does work the datetime value as an integer stored as a character can be used within a variable and returns the correct value

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:30:48 GMT

My example is simply some testcode to check differences and since it is executed within the same program , there is little reason for a difference of 11592 seconds. The value itself is not stored as the datetime() value, but some sort of rounded figure. But why?

I find SAS quite exhausting at times, but that may be due to working with relational databases for 20 years

Calling the start_time variable as you define it does not work @start_time is not recognized, so perhaps the entire code needs to be within a macro?

Re: unable to store datetime value in a macro variable

Cynthia_sas — Sat, 13 Dec 2014 18:34:10 GMT

Hi, Well, I believe that without a PUT and a bigger format for the second SQL where you create the macro variables, you will see scientific notation for the big timestamp numbers, as shown in my 1st screen shot. But you can fix that by using an explicit PUT with a bigger format when you create the macro variables.

However, your bigger problem is that your final PROC SQL does not make sense. Since I ran the program on a different day/time, I have a different timestamp than your program. But my second screen shot illustrates your problem. I would expect more than just the issue you note. With a bigger format, I do see the correct values in the macro variables. And, I can replicate your error message as shown in my second screen shot, because your second SELECT statement is incorrect. WORK.START_FALNR already has all the variables and values you want. I don't understand what the purpose of &max_hits, &process1 and &process2 in your final SQL.

Your issue is NOT that you are unable to store the value, your issue is that the lack of a format caused the incorrect storage of the value. If you used SQL similar to this:

proc sql;
create table work.is_this_what_you_want as
select *, "&max_hits" as mac_max, "&process1" as mac_p1,
"&process2" as mac_p2, "&tstamp" as mac_ts
from work.start_falnr;
quit;

proc print data=work.is_this_what_you_want;
run;

Then you would get something similar to my 3rd screen shot (with different timestamp values, of course). And the only differences are attributable to rounding and formatting. So you could fix minor differences by rounding and/or using the INPUT function to convert the macro variables to numbers. Remember that Macro variables are ALWAYS text, they are ALWAYS treated as text. A reference to a macro variable has to be used with correct syntax. For example, this SQL can be executed:

proc sql;

create table work.new as

select name, 99

from sashelp.class;

quit;

proc print data=work.new;

run;

but it results in the number 99 being assigned the column name of _TEMA001 in WORK.NEW. But the SQL step below creates a new column called NEWVAR and the value for NEWVAR is 99 on every row. Since these examples use SASHELP.CLASS, you should be able to run them easily.

proc sql;

create table work.new2 as

select name, 99 as newvar

from sashelp.class;

quit;

proc print data=work.new2;

run;

So although you *CAN* use a numeric constant of 99 or 238 in a query, it doesn't make sense. And, the reason you got the error message on the timestamp was that there were illegal characters in the field and the ERROR message is telling you that you need a quoted string. You can replicate the error message without using macro variables by trying to run this code and I've included a 4th screen shot that shows the exact same error message that you got when you tried to use your unquoted macro variable:

proc sql;

create table work.new3 as

select name, T99:99:99

from sashelp.class;

quit;

proc print data=work.new3;

run;

My recommendation is that you clarify what your ultimate goal is, rethink how you make the macro variables and possibly rework your 3rd SQL query.

cynthia

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:57:55 GMT

Okay,let me clarify, I put up some code at home , based on the student version on VMWARE and the available tables. (As mentioned)

The actual program I'm working on I cannot access from home, hence the example(s) from VMWARE.

The actual idea is to store the timestamp from the beginning of the process (it takes about 3 minutes to run from begin to end) and then add the timestamp for reporting purposes to the resulting reporting table. (at the end)

The concept of applying a format is not easily adopted after working 20 year with each and every possible relational database, but I think that Cynthia really made it very clear. Thanks for your feedback !

Re: unable to store datetime value in a macro variable

Tom — Sat, 13 Dec 2014 19:21:00 GMT

Your values are truncated because you used PROC SQL to generate the macro variables. If you do not tell it otherwise PROC SQL will used BEST12. to display (and hence convert to macro variables) numeric values. If you are pulling a value from an dataset variable then use the FORMAT= modifier to tell SAS how to convert the number to a string to store into the macro variable. For example:

select stored_time format=best32. into :mymacrovar from mytable ;

If you just want to generate a macro variable for the current DATETIME() then there is no need to write any SQL code. Note that it does not make any sense to use PROC SQL because you are not manipulating any datasets. Most DMBS have tricks to allow you do this using SQL (like select ... from dual in Oracle) because SQL is the only language they have.

If you want to create and manipulate macro variable just use %LET and macro functions.

For example try this.

%let now=%sysfunc(datetime());

%put Raw Datetime =&now;

%put ..............= %sysfunc(putn(&now,datetime24.5));

%put Seconds = %sysfunc(round(&now,1)) ;

%put ..............= %sysfunc(round(&now,1),datetime24.5);

%put Tenths Seconds= %sysfunc(round(&now,.1)) ;

%put ..............= %sysfunc(round(&now,.1),datetime24.5);

%put Minutes = %sysfunc(round(&now,60)) ;

%put ..............= %sysfunc(round(&now,60),datetime24.5);

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 20:59:02 GMT

@Tom, thanks for your reply. My code was merely a random example. The actual code does require the Proc SQL, so another SAS expert suggested the select into option. Thanx also for the examples offered !

Re: unable to store datetime value in a macro variable

Howles — Sun, 14 Dec 2014 16:00:14 GMT

I observe SQL defaulting to BEST8. The DATA step language uses BEST12.

Try this:

data demo ;

numvar = constant('pi') ;

put numvar ;

run ;

proc sql noprint ;

select numvar into : macrovar from demo ;

%put &macrovar ;

quit ;

Re: unable to store datetime value in a macro variable

Ksharp — Mon, 15 Dec 2014 14:08:40 GMT

It might be a default format problem.

data demo ;

numvar = constant('pi') ;

put numvar ;

run ;

proc sql noprint ;

select numvar format=best12. into : macrovar from demo ;

%put &macrovar ;

quit ;

Xia Keshan

Re: unable to store datetime value in a macro variable

SKG — Tue, 01 Mar 2016 07:29:34 GMT

Dear All,

I'm facing the same issue. Actually i'm trying to store datetime value in another variable.

can anyone assist me on the same issue?

I'm mentioning an example:

Proc Sql;

Create Table Work.LastUpdateTable1 As

Select 'AccountBase' as Memname, Max(UpdateTime) as LastUpdatedDate_SAS, Count(Account_ID) as NOBS_SAS From Source.AccountBase;

Quit;

Data Set:-

UpdateTime: 19SEP2015:02:50:45

Output:- Coming

LastUpdateDate_SAS: 1771295413

Re: unable to store datetime value in a macro variable

Mike_B — Fri, 12 Feb 2021 16:01:58 GMT

Thank you! I just spent an hour trying to figure out why my datetime macro variable was showing the wrong day and time, and this fixed it.

Re: unable to store datetime value in a macro variable

Tom — Fri, 12 Feb 2021 16:16:18 GMT

With a modern version of SAS you could add the TRIMMED keyword to make sure the generated macro variable did not contain leading spaces.

So let's make a sample dataset with a datetime variable.

data test;
  start = datetime();
  format start datetime19. ;
run;

Now let's query that to get the raw number of seconds since 1960 into a macro variable.

proc sql noprint;
select start format=32. into :start trimmed from test;
quit;

Results:

20    %put &=start;
START=1928747688