## Use Min and Max dates in the title

Solved
Frequent Contributor
Posts: 129

# Use Min and Max dates in the title

Hi All,

I want to use the Min and Max dates in the title of a report so people know what the date range is.

I have used the Proc SQL to get the MIN and MAX dates. Is this the best method to use - how do I now use those dates in a title for a proc report?

``````Data test;
Input Date \$ Count;
Datalines;

42,921 4
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,926 1
42,940 1
42,940 3
42,940 1
42,940 1
42,940 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
;
Run;

Proc SQL;
Create table work.MinMaxDates AS
Select min(Date) as mindate, max(Date) as maxdate
From Test;
Quit;``````
``````Proc SUmmary data=test sum;
By Date;
VAR Count;
Output out=testsum (DROP=_FREQ_ _TYPE_) SUM=;
RUN;

Title "&mindate to &Maxdate";

Proc Report data=Testsum;
Column Date Count;
Define Date / NOPRINT;
Define Count / Display f=comma8.;
Run;``````

Any help appreciated.

Cheers

Dean

Accepted Solutions
Solution
‎03-19-2018 07:41 PM
Super User
Posts: 13,941

## Re: Use Min and Max dates in the title

Note that MIN and MAX do not work as you might think with character values.

The only way I can see 42,921 as a date is if the values are the exported numeric values of a date from Excel and then imported as character. If that is the case then maybe:

```Data test;
informat date comma6.;
Input Date  Count;
sasdate = '01JAN1900'd + date;
format sasdate date9.;
Datalines;
42,921 4
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,926 1
42,940 1
42,940 3
42,940 1
42,940 1
42,940 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
;
Run;

proc sql;
select put(min(sasdate),date9.),put(max(sasdate),date9.) into :mindate, :maxdate
from test
;
quit;

title "&mindate. to &maxdate.";
```

You may have to adjust the arithmetic for the sasdate variable as I'm too lazy to attempt to adjust such a value. If this is following your data at all I doubt if the sasdate value will be off by more than one day though. The key bit above is how to create a macro variable in sql, the into :macrovar , :macrovar2 and applying a desirable format to an actual SAS date value.

Prior to importing data in SAS from Excel you want to make sure that the column in Excel is set as having a valid date format for all values. Anything that creates "dates" like that is a flawed process at some point.

All Replies
Posts: 1,163

## Re: Use Min and Max dates in the title

Since you have used the macro variables of dates in titles, the best way is to derive the macro variables by proc sql

Hope it is what you are expecting.

``````Proc SQL;
Select min(Date) as mindate, max(Date) as maxdate into: mindate, :maxdate
From Test;
Quit;``````
Thanks,
Jag
PROC Star
Posts: 629

## Re: Use Min and Max dates in the title

Put them into a macro using INTO: in PROC SQL

``````Proc SQL;

Select min(Date) , max(Date) INTO: mindate ,: Maxdate
From Test;
Quit;``````

Now you can call these macros(&mindate, &maxdate) in your titles

Thanks,
Suryakiran
Super User
Posts: 8,216

## Re: Use Min and Max dates in the title

Since those look like SAS dates I think you might want something like:

```Proc SQL;
Select put(input(min(Date),comma8.),date9.),
put(input(max(Date),comma8.),date9.)
into :mindate, :maxdate
From Test
;
Quit;
```

Art, CEO, AnalystFinder.com

Solution
‎03-19-2018 07:41 PM
Super User
Posts: 13,941

## Re: Use Min and Max dates in the title

Note that MIN and MAX do not work as you might think with character values.

The only way I can see 42,921 as a date is if the values are the exported numeric values of a date from Excel and then imported as character. If that is the case then maybe:

```Data test;
informat date comma6.;
Input Date  Count;
sasdate = '01JAN1900'd + date;
format sasdate date9.;
Datalines;
42,921 4
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,922 1
42,926 1
42,940 1
42,940 3
42,940 1
42,940 1
42,940 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
42,941 1
;
Run;

proc sql;
select put(min(sasdate),date9.),put(max(sasdate),date9.) into :mindate, :maxdate
from test
;
quit;

title "&mindate. to &maxdate.";
```

You may have to adjust the arithmetic for the sasdate variable as I'm too lazy to attempt to adjust such a value. If this is following your data at all I doubt if the sasdate value will be off by more than one day though. The key bit above is how to create a macro variable in sql, the into :macrovar , :macrovar2 and applying a desirable format to an actual SAS date value.

Prior to importing data in SAS from Excel you want to make sure that the column in Excel is set as having a valid date format for all values. Anything that creates "dates" like that is a flawed process at some point.

Super User
Posts: 8,216

## Re: Use Min and Max dates in the title

Agreed! They do look more like Excel dates. But then @DME790 could use:

```Proc SQL;
Select put(input(min(Date),comma8.)-21916,date9.),
put(input(max(Date),comma8.)-21916,date9.)
into :mindate, :maxdate
From Test
;
Quit;
```

Art, CEO, AnalystFinder.com

Frequent Contributor
Posts: 129

## Re: Use Min and Max dates in the title

Thanks ballardw - works perfectly.

Sorry about the confusion with the dates - I couldn't get the dates in properly in the test data so I used the numeric value instead.

The data comes from SAS tables so the field is set to a date value.

Thanks heaps.

Dean

☑ This topic is solved.