Use Min and Max dates in the title

[DME790]

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

[Jagadishkatam]

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;

[SuryaKiran]

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

[art297]

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

[ballardw · Accepted solution]

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.

[art297]

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

[DME790]

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