Desktop productivity for business analysts and programmers

Use Min and Max dates in the title

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 108
Accepted Solution

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,008

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.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,146

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
Super Contributor
Posts: 448

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
PROC Star
Posts: 8,104

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,008

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.

PROC Star
Posts: 8,104

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: 108

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 250 views
  • 5 likes
  • 5 in conversation