topic Re: pull min of datetime in SAS Programming

pull min of datetime

chinna0369 — Fri, 23 Aug 2019 23:58:32 GMT

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

Re: pull min of datetime

novinosrin — Sat, 24 Aug 2019 00:04:15 GMT

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

Re: pull min of datetime

Kurt_Bremser — Sat, 24 Aug 2019 06:15:26 GMT

The proper informat for ISO datetime values is E8601DT16.

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

Re: pull min of datetime

Tom — Sat, 24 Aug 2019 16:44:34 GMT

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.

Re: pull min of datetime

Tom — Sat, 24 Aug 2019 17:35:40 GMT

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.

Re: pull min of datetime

ChrisNZ — Mon, 26 Aug 2019 00:11:48 GMT

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

Re: pull min of datetime

chinna0369 — Mon, 26 Aug 2019 16:29:12 GMT

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

Re: pull min of datetime

ballardw — Mon, 26 Aug 2019 16:37:04 GMT

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

Re: pull min of datetime

chinna0369 — Mon, 26 Aug 2019 16:46:49 GMT

Throwing below error.

Re: pull min of datetime

Kurt_Bremser — Mon, 26 Aug 2019 17:26:50 GMT

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.

Re: pull min of datetime

chinna0369 — Mon, 26 Aug 2019 17:42:06 GMT

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

Re: pull min of datetime

chinna0369 — Mon, 26 Aug 2019 17:42:43 GMT

Please let me know if you need anything.

Thanks,

Adi

Re: pull min of datetime

Tom — Mon, 26 Aug 2019 17:51:35 GMT

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

Re: pull min of datetime

Tom — Mon, 26 Aug 2019 18:34:23 GMT

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

Re: pull min of datetime

Kurt_Bremser — Tue, 27 Aug 2019 07:56:32 GMT

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.