pull min of datetime

[chinna0369]

Hi all, 

I have data like below:

I want to take min from date (character) variable and I need that in following format " 27AUG2018:15:07:00" and format should be datetime20.?

And I am using below code, but it is not giving exact values.

proc sql;
create table chk as
select usubjid, min(input(date,anydtdtm20.)) as want format datetime20.
from data
group by usubjid;
quit;

Could you please correct me with modified code.

Thanks,

Adithya

[novinosrin]

Can you please paste data as plain text rather than pictures? Also, post the log if you can

[Kurt_Bremser]

The proper informat for ISO datetime values is E8601DT16.

Please supply example data in usable form, in a data step with datalines.

[Tom]

I want to take min from date (character) variable and I need that in following format " 27AUG2018:15:07:00" and format should be datetime20.?

You seem to be using the word FORMAT in two different ways in that sentence.  In some computer languages/systems they use the word format to mean the type of the variable.  In SAS there are just two types of variables, fixed length character strings and floating point numbers.  In SAS terminology format is just the instructions for how to convert the stored value into text. The DATETIME20. format is a numeric format since its name does not begin with $, so it will work on floating point numbers.  The DATETIME format will interpret the number as the number of seconds since 1960 and display it in the style of the string you have in quotes using 20 characters because you set a width of 20.  But the variable that the format is attached to (or applied to) is still just a number.

[Tom]

Your code looks fine to me.  I would normally put an equal sign between the FORMAT keyword and the actual format specification, but I don't think it is required.

Are you getting errors?

Are you getting values that don't match what you expect? 

If so provide the LOG and example input explain how the results do not match.  Please reduce the example data to just enough to demonstrate the problem and post it as SAS code and not snapshots.

[ChrisNZ · Accepted solution]

This works:

data HAVE; 
  CHAR='2008-09-15T15:53:00';
run;
proc sql;
  select  min(input(CHAR,e8601dt.)) as MIN format=datetime20.
  from HAVE ;
quit;

MIN
15SEP2008:15:53:00

[chinna0369]

Hi ChrisNZ, 

Thank you for your reply!

Yes, it is working but I am getting below invalid note in log, how can I avoid that?

Thank you all for your reply!

Thanks,

Adi

[ballardw]

---

@chinna0369 wrote:

Hi ChrisNZ, 

Thank you for your reply!

Yes, it is working but I am getting below invalid note in log, how can I avoid that?

Thank you all for your reply!

Thanks,

Adi

---

If the DATE variable is already a SAS datetime value then attempting to INPUT it that way is invalid as INPUT requires a CHARACTER value and SAS date, time and datetime values are numeric.

See what happens if you use:  min(date) as min format=datetime20.

[chinna0369]

Throwing below error.

[Kurt_Bremser]

Maxim 3: KNOW YOUR DATA!

Types, lengths, formats and contents.

Since you still refuse to give us example data in a usable, unambiguous form (data step with datalines), it is very hard to help you, because you KEEP US GUESSING.

All we see is pictures(!) of logs of failed steps. Copy/paste log text into a window opened with the {i} button, making screen shots is not necessary for that.

Please show the output of a proc contents of your dataset chk, and a proc print of a few observations of your dataset.

[chinna0369]

Hi KurtBremser, 

Below is the sample data:

data chk;
input subject $15. date $50.;
datalines;
CA2099DX1304 2019-06-13T11:57
CA2099DX101124 2018-11-30T13:15
CA2099DX101124 2018-12-28T11:02
CA2099DX101124 2019-01-25T11:12
CA2099DX101124 2019-02-22T11:45
CA2099DX101124 2019-03-22T11:59
CA2099DX101124 2019-04-18T11:33
CA2099DX101124 2019-05-16T12:13
CA2099DX101124 2019-06-14
CA2099DX10344 2018-08-22T15:00
CA2099DX10344 2018-09-21T12:14
CA2099DX10344 2018-10-19T12:02
CA2099DX10344 2018-11-16T13:30
CA2099DX10344 2018-12-14T10:00
CA2099DX10344 2019-01-11T13:56
CA2099DX10344 2019-02-08T09:49
CA2099DX10344 2019-03-08T12:20
CA2099DX10344 2019-04-05T10:54
CA2099DX10344 2019-05-03T12:30
;
run;

and below is the output of a proc contents of dataset chk.

Thanks,

Adi

[Tom]

It does not look like the ANYDTDTM informat reads that properly. But the B8610DT informat seems to work fine.
input(date,B8601DT16.)

[Tom]

Someone might want to enter a SAS ballot entry to enhance the ANYDTDTM informat to handle that data format.

data chk;
  input subject :$15. date :$50.;
  dt1=input(date,B8601DT16.);
  dt2=input(date,anydtdtm16.);
  format dt: datetime20.;
datalines;
CA2099DX101124 2018-11-30T13:15
CA2099DX101124 2019-06-14
;

proc print;
run;

    Obs       subject        date                                 dt1                     dt2

     1     CA2099DX101124    2018-11-30T13:15      30NOV2018:13:15:00                       .
     2     CA2099DX101124    2019-06-14            14JUN2019:00:00:00      14JUN2019:00:00:00

[Kurt_Bremser]

It is very interesting that the b8601dt informat works with the short date, while e8601dt does not. It is also interesting that b8601dt works at all, as "basic" ISO datetimes look like this:

20180915T155300

and

2018-11-30T13:15

constitutes the "extended" notation.

I am contemplating opening a track with SAS TS.

[chinna0369]

Please let me know if you need anything. 

Thanks,

Adi