loops - SAS: How can I filter for (multiple) entries which are closest to the last day of month (for each month) -

i have large dataset , want filter rows date entry closest last day of month, each month. there multiple entries day closest last day of month.

so instance: original dataset

date          price       name 05-01-1995    1,2         abc 06-01-1995    1,5         def 07-01-1995    1,8         ghi 07-01-1995    1,7         mmm 04-02-1995    1,9         jkl 27-02-1995    2,1         mno 

goal:

date          price       name 07-01-1995    1,8         ghi 07-01-1995    1,7         mmm 27-02-1995    2,1         mno 

i had 2 ideas, failing implementing within loop (for traversing months) in sas. 1.idea: create new column wich indicates last day of current month (intnx() function); filter entries closest last day of month:

date          price       name   last_day_of_month 05-01-1995    1,2         abc    31-01-1995 06-01-1995    1,5         def    31-01-1995 07-01-1995    1,8         ghi    31-01-1995 04-02-1995    1,9         jkl    28-02-1995 27-02-1995    2,1         mno    28-02-1995 

2.idea: filter each month entries highest date (using maybe max function?!)

i glad if able me, used ordinary programming languages , started sas research purposes.

proc sql 1 way solve kind of situation. i'll break down original requirements explanations in how interpret them in sql.

since want group observations on date, can use having clause filter on max date per month.

data work.have; input date ddmmyy10. price name $; format date date9.; datalines; 05-01-1995    1.2         abc 07-01-1995    1.8         ghi 06-01-1995    1.5         def 07-01-1995    1.7         mmm 04-02-1995    1.9         jkl 27-02-1995    2.1         mno ;  data work.want; input date ddmmyy10. price name $; format date date9.; datalines; 07-01-1995    1.8         ghi 07-01-1995    1.7         mmm 27-02-1995    2.1         mno ;  proc sql ; create table work.want   select *     /*, max(date) max_date format=date9.*/     /*, intnx('month',date,0,'end') monthend format=date9.*/   work.have   group intnx('month',date,0,'end')   having max(date) = date   order date, name ; 

if uncomment comments, actual filters used shown in output table.

comparing the requirements against solution:

proc compare base=work.want compare=work.solution; 

results in

note: no unequal values found. values compared equal.