Formatting of sas datetime

[Kiteulf]

When i write

earliest is :

proc sort data=data out=earliest; by appln_dttm; run;

then I run;

Proc sql noprint;
select appln_dttm into :Min_date 
from earliest 

;
quit;
%put &Min_date;

I get the result;

 %put &Min_date;
03MAR2022:00:01:11.000000

However if I run;

Proc sql noprint;
select min(appln_dttm) into :Min_date2 
from data

;
quit;
%put &Min_date2;

I get;

%put &Min_date2;
1.9619E9

These are two different datetimes. which I suspect has something to do with the format of the appln_dttm variable.It looks like it formatted in different ways even though it is 

[Kiteulf]

If I use the following then it will read the format straight and the actual first date:

Proc sql noprint;
select min(put(appln_dttm,anydtdtm.)) into :Min_date2 
from data

;
quit;
%put &Min_date2;

[Kiteulf]

How do I convert the source appln_dttm to one true readable sas datetime format?

[Patrick · Accepted solution]

---

@Kiteulf wrote:

How do I convert the source appln_dttm to one true readable sas datetime format?

---

The value in appln_dttm is a count of seconds since 1/1/1960. Use a format to instruct SAS how to convert this number to a human readable text string when populating the macro variable.

Proc sql noprint;
  select min(appln_dttm) format=datetime21. into :Min_date2 
    from data
  ;
quit;
%put &Min_date2;

In your first code sample SAS uses implicitly the already applied datetime format from the source variable. In your second example and because of the additional min() function this doesn't happen anymore "automatically" and though you need to assign such a format in the code. 

[Kiteulf]

However there is still the issue of adding back this date to another set of timestamps

DATA new_sample2;
set new_sample;
where appln_dttm ge &Min_date2 ;
run;

28 DATA new_sample2;
29 set new_sample;

30 where appln_dttm ge &Min_date2 ;
NOTE: Line generated by the macro variable "MIN_DATE2".
30 03MAR2022:00:01:11.000000
_______
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, GE, GT, LE,
LT, NE, NOT, OR, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

31 run;

31 run;

[PaigeMiller]

When you use a macro variable, its value is substituted into your code, and the result must be valid legal working SAS code.

So, when you write

DATA new_sample2;
set new_sample;
where appln_dttm ge &Min_date2 ;
run;

and run it, SAS actually runs this code:

DATA new_sample2;
set new_sample;
where appln_dttm ge 03MAR2022:00:01:11.000000;
run;

This is not valid legal working SAS code and so an error appears. Can you see why this is not valid SAS code? What would you need to do to fix this so it does work?

Hint: when using macros and macro variables, it is critical that you have working SAS code for one or two iterations. If you don't have working SAS code, then adding macro variables or macros will not fix the problem, the code still won't work, and the problem is not the macro or macro variable.

[Tom]

---

@Kiteulf wrote:

If I use the following then it will read the format straight and the actual first date:

Proc sql noprint;
select min(put(appln_dttm,anydtdtm.)) into :Min_date2 
from data

;
quit;
%put &Min_date2;

---

That is not going to work at all.  There is no format named ANYDTDTM.  What would that even mean?  Should it convert the datetime numeric value into some random style of human readable datetime stamp?

You want to take the minimum of the actual value, not the formatted value.  Unless you are using E8601 formatted datetime values where components are listed in decreasing magnitude of the time units, Year, Month, Day, Hour, Minute, Second then sorted the textual version will not find the earliest time point.

If you are going to use the value in SAS code, like a WHERE statement then just put the raw number of seconds into the macro variable, just don't let PROC SQL use its default format of BEST8 when generating the macro variable.

select min(appln_dttm) format=best32.
  into :Min_date2 trimmed 
from data
;