finding min and max date from multiple dates

[Rahul_SAS]

Hi Experts,

please help me to find a max and a min date value from 10 date fields (date1, date2, date3...date10). And further, need to get all intermediate months between those two min and max date in a new column.

thanks

Rahul

[Jagadishkatam]

Could  you please provide sample data and expected out for better response.

[Rahul_SAS]

please find the sample data and required result.

[Kurt_Bremser]

@Rahul_SAS , with more than 100 posts here, you should know that posting Excel spreadsheets is not the way to supply example SAS datasets.

Please use a data step with datalines, as you have already been asked multiple times and shown how to do it, eg here: https://communities.sas.com/t5/SAS-Programming/multiple-firs-var-not-working/m-p/500913/highlight/tr...

[Kurt_Bremser]

Your data makes NO sense.

Your "have.xlsx" contains only dates in June 2019, while your "want.xlsx" goes back to February 2018 and ends in July 2019. Please make up your mind, and supply USABLE example data that REALLY illustrates what you want, and give us the REAL logic behind it.

See Maxim 42.

Right now, my answer can only be "42".

[Reeza]

What do you mean by intermediate months? Are those the months that are present in the data or you want all months?

I would recommend first transposing your data to a long format and then doing this, it's easier to work with and you can use BY group processing.

[andreas_lds]

If you need min and max per observation, you can use something like

min = min(of date1-date10);
max = max(of date1-date10);

Assuming that you have proper sas-dates.

[Rahul_SAS]

no, its not per observation.

I need to have min and max date from across all date fields.

[andreas_lds]

---

@Rahul_SAS wrote:

no, its not per observation.

I need to have min and max date from across all date fields.

---

Then please post data in usable form and show the output you expect form this data.

[Jagadishkatam]

You may try the below code and WANT3 will have the expected results.

data want;
set have;
min=min(of date1-date5);
max=max(of date1-date5);
format min max date9.;
run;

proc sql;
create table want2 as select min(min) as min2 format=date9., max(max) as max2 format=date9. from want ;
quit;

data want3;
set want2;
do date=min2 to max2;
monyear=put(date,monyy7.);
output;
end;
format date date9.;
run;

[hashman]

@Jagadishkatam :

You can do:

do date=min2 to max2;
monyear=put(date,monyy7.);
output;
end;

But then you'll have to dedup MONYEAR since DATE iterates through each date from MIN2 to MAX2, and hence MONYEAR will be the same for every day of the same month. 

[hashman]

@Rahul_SAS :

If you'd like to get clear help, you have to express your request clearly, too. Field=variable. Observation (i.e. record or row) consist of variables, i.e. of fields. Hence, "per observation" means "across fields in this observation". The way you've formulated your question, it looks like this picture:

   date1      date2      date3      date4      date5     mindate    maxdate 
----------------------------------------------------------------------------
2014-12-11 2015-09-24 2015-02-27 2015-01-07 2015-09-06 2014-12-11 2015-09-24
2015-09-23 2015-04-21 2015-04-17 2014-10-23 2014-10-29 2014-10-23 2015-09-23
2015-07-31 2015-04-14 2015-08-12 2014-10-29 2015-09-19 2014-10-29 2015-09-19
2015-01-21 2015-01-12 2015-06-13 2015-09-26 2014-12-26 2014-12-26 2015-09-26
2015-06-13 2015-03-04 2015-04-26 2015-01-17 2015-03-27 2015-01-17 2015-06-13

If this is different from what you have in mind, show your picture. As they say, it's worth a thousand words.

Kind regards

Paul D.     

[hashman]

@Rahul_SAS :

I'm not sure whether your "get all intermediate months between those two min and max date in a new column" means the inclusive number of months of their list in some date format. Either way, try this (you haven't provided any sample data, so I've just mocked up 5 dates date1-date5 in the first step):

data have ;                                                                   
  array date date1-date5 ;                                                    
  do _n_ = 1 to 5 ;                                                           
    do over date ;                                                            
      date = 20000 + ceil (ranuni(1) * 365) ;                                 
    end ;                                                                     
    output ;                                                                  
  end ;                                                                       
  format date: yymmdd10. ;                                                    
run ;                                                                         
                                                                              
data want ;                                                                   
  set have ;                                                                  
  mindate = min (of date:) ;                                                  
  maxdate = max (of date:) ;                                                  
  N_months = intck ("mon", mindate, maxdate) ;                                
  length months $ 32767 ;                                                     
  do _n_ = 0 to N_months ;                                                    
    months = catx (", ", months, put (intnx ("mon", mindate, _n_), yymmd7.)) ; 
  end ;                                                                       
  format mindate maxdate yymmdd10. ;                                          
run ;                                                                         

Kind regards

Paul D.