Limiting Number of Values Used in PROC Report

[tburus]

I am trying to build a table with PROC Report that summarizes results for the last three years from a dataset containing results from the last 10 years. I am unsure how (if possible) to limit the number of years output.

My current code is given below:

proc report data=participating_programs; 
	column sport num_participating_programs,year;
	define sport / group f=$sport.;
	define year / across order=internal;
	define num_participating_programs / analysis sum '';
run;

[Astounding]

Limit observations by adding a WHERE statement to the code. For example you can add:

where (2017 <= year <= 2019);

[tburus]

Okay, two things. One, my 'year' variable is a character variable. Two, I need to automate this to be run yearly (I will probably need to define a macro for this, which is fine).

[Astounding]

That can all be done, just a little cumbersome:

where put(year(today())-3, 4.) <= year <= put(year(today())-1, 4.);

[tburus]

Okay, I'm getting what I want now, but the way I'm doing it seems like it's going to be a mess to automate. Is there a way to do this using LAST.variable and counting back two?

[Kurt_Bremser · Accepted solution]

Year as character needs 4 bytes. A complete SAS date can also be stored in 4 bytes, and a numeric year in the minimum of 3 bytes. So you should seriously reconsider your data design, as numeric values are easier to handle for such cut-off issues and the necessary calculations:

proc sql noprint;
select max(year) into :maxyear from have;
quit;

and later

where &maxyear. - 2 le year le &maxyear.;

[tburus]

Thanks. This almost gets me there. Unfortunately the 'Year' variable I receive is in the form "1819" for 2018-19, "1920" for 2019-20, etc. How might I go about converting this to a numeric? Then I could use your idea by adding and subtracting 101.

[smantha]

split the value and define low and high

low='20'||substr(year,1,2)

high='20'||substr(year,3,2)

[tburus]

I was able to use this as a starting point to create appropriate macros following a predictable pattern in my data. Thanks.

[ballardw]

---

@tburus wrote:

 One, my 'year' variable is a character variable.

---

Almost always a poor idea.

And with any requirement to "automate" something related to dates then you really should use SAS date values. Then there are a bunch of tools available to manipulate the dates. With character variables the first thing you would have to do for something like " I want all of the records within the previous 3 years of today" is turn the values into dates. Save the effort later and do it once when values are created/ read.

Where year(datevariable) ge (year(today()) -3)

for instance.