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;
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.;
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).
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?
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.;
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.
split the value and define low and high
low='20'||substr(year,1,2)
high='20'||substr(year,3,2)
I was able to use this as a starting point to create appropriate macros following a predictable pattern in my data. Thanks.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.