1

I have a yearly survey that I do some validations on. I only want to do the validations for the current year (max year in the column surveyYear). The survey comes every year so I want to have it automatised instead of writing the current year. So when the survey for 2019 comes I do not need to change the code.

Is it possible to filter surveyYear in the set statement to only take the largest number in the column? I have tried different combinations but do not sucseed. And if it is not possible, how could I solve it otherwise

I would also like to do it in a datastep and not in proc as the validation code comes in the want datastep after the set statement.

data have;
input surveyYear id score varChar$10. ;
datalines;
2016 1      10     Yes
2016 2      6      Yes
2016 3      8      Yes
2016 4      .      No
2017 5      6      No
2017 6      5      No
2017 7      12     IU
2017 8      3      IU
2017 9      2      IU
2017 10     15     99999
2018 11     0      .
2018 12     .      No
2018 13     10     Yes
2018 14     8      No
2018 15     11     No
2018 16     3      IU
;
run; 

data want;
set have (where=(surveyYear > 2017));
/* I would like to have something that states the max surveyYear instead */
/* (where=(surveyYear = max(surveyYear))) */
run;
aef
  • 35
  • 8

1 Answers1

0

To filter for the max you can first get hte max and then apply a filter.

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

Then use it in your filter.

data want;
set have (where=(year=&latest_year.));
Reeza
  • 20,510
  • 4
  • 21
  • 38
  • Thanks, that did the trick! I just changed year to surveyYear. Is it even possible to do something like (where=(surveyYear = max(surveyYear))) in SAS or does one really need to go thru the proc? – aef Nov 02 '18 at 17:17
  • It depends. For a SQL query it would be in the HAVING clause, and for a data set option, you have to remember that the MAX and other functions only operate on a row of data at a time, not a column. – Reeza Nov 02 '18 at 17:25
  • Handling one row at a time makes SAS super powerful, but different from other languages and how math is taught. – Reeza Nov 02 '18 at 17:26