topic Re: How to drop the variables that doesn't contain fully monthly data for every singe year in New SAS User

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

Danny2020 — Thu, 28 Jan 2021 04:48:37 GMT

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

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

PeterClemmensen — Thu, 28 Jan 2021 05:47:54 GMT

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

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

Danny2020 — Thu, 28 Jan 2021 06:03:09 GMT

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

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

PeterClemmensen — Thu, 28 Jan 2021 06:18:29 GMT

Anytime 🙂

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

Danny2020 — Fri, 29 Jan 2021 03:25:05 GMT

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.