How to drop the variables that doesn't contain fully monthly data for every singe year

[Danny2020]

I am a very new learner in sas, just meet some trouble on how to delete unneeded data values. Here is the situation; I have different fund ID, but some of them don't contain fully monthly data for a whole year (e.g. like the row in green), how can i keep the data that only contains fully monthly value （from 12 to 1) based on fund ID and Year. Many thanks!!

ID	Return	Size	Year	Month
1	0.26	Small	2020	12
1	0.24	Small	2020	11
1	0.96	Small	2020	10
1	-1.18	Small	2020	9
1	2.1	Small	2020	8
1	-0.08	Small	2020	7
1	2.28	Small	2020	6
1	1.51	Small	2020	5
1	-1.97	Small	2020	4
1	-1.19	Small	2020	3
1	-0.64	Small	2020	2
1	2.22	Small	2020	1
1	1.13	Small	2019	12
1	-1.6	Small	2018	12
1	0.86	Small	2018	11
1	1.35	Small	2018	10
1	-1.33	Small	2018	9
1	3.9	Small	2018	8
1	-1.99	Small	2018	7
2	8.26	Small	2020	11
2	-2.36	Small	2020	10
2	-0.7	Small	2020	9
2	2.32	Small	2020	8
2	3.77	Small	2020	7
2	-2.11	Small	2020	6
2	4.85	Small	2020	5
2	6.11	Small	2020	4

[PeterClemmensen · Accepted solution]

@Danny2020  Hi and welcome to the SAS Community 🙂

Thank you for a clear question and sample data. Try this

Feel free to ask.

data have;
input ID Return Size $ Year Month;
datalines;
1 0.26 Small 2020 12
1 0.24 Small 2020 11
1 0.96 Small 2020 10
1 -1.18 Small 2020 9
1 2.1 Small 2020 8
1 -0.08 Small 2020 7
1 2.28 Small 2020 6
1 1.51 Small 2020 5
1 -1.97 Small 2020 4
1 -1.19 Small 2020 3
1 -0.64 Small 2020 2
1 2.22 Small 2020 1
1 1.13 Small 2019 12
1 -1.6 Small 2018 12
1 0.86 Small 2018 11
1 1.35 Small 2018 10
1 -1.33 Small 2018 9
1 3.9 Small 2018 8
1 -1.99 Small 2018 7
2 8.26 Small 2020 11
2 -2.36 Small 2020 10
2 -0.7 Small 2020 9
2 2.32 Small 2020 8
2 3.77 Small 2020 7
2 -2.11 Small 2020 6
2 4.85 Small 2020 5
2 6.11 Small 2020 4
;

data want(drop = c);
   do c = 1 by 1 until (last.year);
      set have;
      by id descending year;
   end;
   do until (last.year);
      set have;
      by id descending year;
      if c = 12 then output;
   end;
run;

[Danny2020]

that works very well, you are amazing , many thanks!!! xxoo

[PeterClemmensen]

Anytime 🙂

[Danny2020]

Hey Peter, now my professor change his criteria on cleansing up those data, instead of keep fully 12-monthly data for every year, he suggested me to keep those years that contain----equal or more than 10-monthly data (i.e. 12-monthly data, 11-monthly data and 10-monthly data), in which the 10 months or 11 months do not need be in sequence, as long as there are 10-monthly or 11-monthly values in a year, then keep that year. The final dataset still will be based on fund ID and Year. Could you kindly give me a hand? I am so stressful. Many thanks.