12-13-2014 11:23 AM
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 )
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
proc sql noprint;
select max_hits,process_start_integer,process_start,tstamp into
create table work.output as
select process_start_integer,&max_hits,&process1,&process2,&tstamp from work.start_falnr;
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 ...
12-13-2014 01:23 PM
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
12-13-2014 01:30 PM
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?
12-13-2014 02:21 PM
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.
%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);
12-13-2014 03:59 PM
@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 !
12-14-2014 11:00 AM
I observe SQL defaulting to BEST8. The DATA step language uses BEST12.
data demo ;
numvar = constant('pi') ;
put numvar ;
proc sql noprint ;
select numvar into : macrovar from demo ;
%put ¯ovar ;
12-15-2014 09:08 AM
It might be a default format problem.
data demo ;
numvar = constant('pi') ;
put numvar ;
proc sql noprint ;
select numvar format=best12. into : macrovar from demo ;
%put ¯ovar ;
12-13-2014 01:11 PM
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());
12-13-2014 01:34 PM
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:
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
proc print data=work.is_this_what_you_want;
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:
create table work.new as
select name, 99
proc print data=work.new;
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.
create table work.new2 as
select name, 99 as newvar
proc print data=work.new2;
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:
create table work.new3 as
select name, T99:99:99
proc print data=work.new3;
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.
12-13-2014 01:57 PM
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 !
03-01-2016 02:29 AM
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:
Create Table Work.LastUpdateTable1 As
Select 'AccountBase' as Memname, Max(UpdateTime) as LastUpdatedDate_SAS, Count(Account_ID) as NOBS_SAS From Source.AccountBase;