Desktop productivity for business analysts and programmers

how to subset with min and max and other combinations of dates in the data?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

how to subset with min and max and other combinations of dates in the data?

I have the following dates data:

 

 

data mydates;
  infile cards truncover expandtabs;
  input Date :date9. time :time8.;
  dt = date * 86400 + time;
  format Date date9. time time8. dt datetime19.;
cards;
02Jan2016 18:50:46
04Jan2016 0:08:47
04Jan2016 5:40:23
05Jan2016 11:58:24
12Jan2016 10:06:35
24Jan2016 17:49:23
26Jan2016 9:58:08
29Jan2016 9:58:02
30Jan2016 11:48:34
02Feb2016 13:10:07
04Feb2016 9:12:03
04Feb2016 20:29:06
05Feb2016 11:18:21
06Feb2016 10:24:24
07Feb2016 1:09:35
08Feb2016 10:45:27
09Feb2016 13:10:47
10Feb2016 13:07:53
11Feb2016 13:15:14
12Feb2016 9:35:45
13Feb2016 1:11:07
15Feb2016 9:53:32
16Feb2016 9:51:57
29Jun2016 13:15:14
29Jun2016 9:35:45
06Jul2016 1:11:07
06Jul2016 9:53:32
22Sep2016 9:51:57

; run;

 

 

When I am trying to create a subset I used this... but I think this is not correct...

 

 

       PROC SQL;
             SELECT t1.*
                FROM mydates t1
                WHERE t1.Date IN
                     (
                     min(t1.Date),
'29Jun2016'd, '6Jul2016'd, max(t1.Date) ); QUIT;

 

Please help me to write correct code.

 

Thanks.

 


Accepted Solutions
Solution
‎12-06-2016 07:15 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: how to subset with min and max and other combinations of dates in the data?

Hi,

 

proc sql;
  create table WANT as
  select  *
  from    MYDATES
  having  DATE=min(DATE) or DATE=max(DATE);
quit;

Pretty sure I mentioned before, but if not, its not a good idea to call columns DATE or TIME, or DATETIME, expecially when you call a variable DATE and its a datatime field.  No need to code all in caps either.

 

 

 

View solution in original post


All Replies
Solution
‎12-06-2016 07:15 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,188

Re: how to subset with min and max and other combinations of dates in the data?

Hi,

 

proc sql;
  create table WANT as
  select  *
  from    MYDATES
  having  DATE=min(DATE) or DATE=max(DATE);
quit;

Pretty sure I mentioned before, but if not, its not a good idea to call columns DATE or TIME, or DATETIME, expecially when you call a variable DATE and its a datatime field.  No need to code all in caps either.

 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 148 views
  • 1 like
  • 2 in conversation