topic Re: finding min and max date from multiple dates in SAS Programming

finding min and max date from multiple dates

Rahul_SAS — Wed, 07 Aug 2019 20:07:22 GMT

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

Re: finding min and max date from multiple dates

Jagadishkatam — Wed, 07 Aug 2019 20:51:51 GMT

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

Re: finding min and max date from multiple dates

Reeza — Wed, 07 Aug 2019 21:15:05 GMT

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.

Re: finding min and max date from multiple dates

andreas_lds — Thu, 08 Aug 2019 04:32:50 GMT

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.

Re: finding min and max date from multiple dates

Rahul_SAS — Thu, 08 Aug 2019 05:20:38 GMT

please find the sample data and required result.

Re: finding min and max date from multiple dates

Rahul_SAS — Thu, 08 Aug 2019 05:22:58 GMT

no, its not per observation.

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

Re: finding min and max date from multiple dates

hashman — Thu, 08 Aug 2019 05:52:54 GMT

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

Re: finding min and max date from multiple dates

Kurt_Bremser — Thu, 08 Aug 2019 06:06:44 GMT

@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/true#M133472

Re: finding min and max date from multiple dates

andreas_lds — Thu, 08 Aug 2019 06:15:33 GMT

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

Re: finding min and max date from multiple dates

Jagadishkatam — Thu, 08 Aug 2019 06:20:13 GMT

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;

Re: finding min and max date from multiple dates

hashman — Thu, 08 Aug 2019 06:50:50 GMT

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

Re: finding min and max date from multiple dates

hashman — Thu, 08 Aug 2019 07:43:22 GMT

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

Re: finding min and max date from multiple dates

Kurt_Bremser — Thu, 08 Aug 2019 08:24:16 GMT

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