topic Re: Use Min and Max dates in the title in SAS Enterprise Guide

Use Min and Max dates in the title

DME790 — Mon, 19 Mar 2018 23:04:51 GMT

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

Re: Use Min and Max dates in the title

Jagadishkatam — Mon, 19 Mar 2018 23:19:37 GMT

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;

Re: Use Min and Max dates in the title

SuryaKiran — Mon, 19 Mar 2018 23:22:02 GMT

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

Re: Use Min and Max dates in the title

art297 — Mon, 19 Mar 2018 23:23:10 GMT

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

Re: Use Min and Max dates in the title

ballardw — Mon, 19 Mar 2018 23:23:59 GMT

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.

Re: Use Min and Max dates in the title

art297 — Mon, 19 Mar 2018 23:31:40 GMT

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

Re: Use Min and Max dates in the title

DME790 — Mon, 19 Mar 2018 23:45:34 GMT

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