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 |
@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 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;
Anytime 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.