topic Re: Limiting Number of Values Used in PROC Report in SAS Programming

Limiting Number of Values Used in PROC Report

tburus — Wed, 10 Jun 2020 06:06:03 GMT

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;

Re: Limiting Number of Values Used in PROC Report

Astounding — Wed, 10 Jun 2020 06:35:14 GMT

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

where (2017 <= year <= 2019);

Re: Limiting Number of Values Used in PROC Report

tburus — Wed, 10 Jun 2020 06:48:18 GMT

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).

Re: Limiting Number of Values Used in PROC Report

Astounding — Wed, 10 Jun 2020 06:54:18 GMT

That can all be done, just a little cumbersome:

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

Re: Limiting Number of Values Used in PROC Report

tburus — Wed, 10 Jun 2020 07:02:35 GMT

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?

Re: Limiting Number of Values Used in PROC Report

Kurt_Bremser — Wed, 10 Jun 2020 09:26:16 GMT

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.;

Re: Limiting Number of Values Used in PROC Report

tburus — Wed, 10 Jun 2020 13:48:44 GMT

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.

Re: Limiting Number of Values Used in PROC Report

smantha — Wed, 10 Jun 2020 13:53:24 GMT

split the value and define low and high

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

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

Re: Limiting Number of Values Used in PROC Report

ballardw — Wed, 10 Jun 2020 14:00:18 GMT

@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.

Re: Limiting Number of Values Used in PROC Report

tburus — Thu, 11 Jun 2020 02:24:18 GMT

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