BookmarkSubscribeRSS Feed
blom0344
Calcite | Level 5


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   ...

13 REPLIES 13
Hercules
Fluorite | Level 6

Use:

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

then use this anywhere as an macro variable @datetime_var

Hope this help.

blom0344
Calcite | Level 5

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

blom0344
Calcite | Level 5

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  Smiley Happy

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?

Tom
Super User Tom
Super User

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);

blom0344
Calcite | Level 5

@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  !

Howles
Quartz | Level 8

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 ;

Ksharp
Super User

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

Mike_B
Obsidian | Level 7
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.
Tom
Super User Tom
Super User

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

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());

Cynthia_sas
SAS Super FREQ

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


baseball2_bad_select.pngbaseball4_unquoted_string_in_select.pngbaseball3_good_select.pngbaseball_question1.png
blom0344
Calcite | Level 5

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 !

SKG
Obsidian | Level 7 SKG
Obsidian | Level 7

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

 


DataSet.pngOutput.png

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 13 replies
  • 13335 views
  • 8 likes
  • 8 in conversation