Hi all,
I have longitudinal monthly data and want to use only those rows of data for an ID where all 12 months in a year are available. So for the following, I want to exclude 11/2005 and 12/2005 for ID=49, and exclude 1/2007 and 2/2007 for ID=80.
I would really appreciate some suggestions - I experimented with first.ID and last.ID to no avail.Thanx. jm.
ID | date | year | month | ret |
49 | 1-Nov-05 | 2005 | 11 | 2.65188 |
49 | 1-Dec-05 | 2005 | 12 | 0.0682 |
49 | 1-Jan-06 | 2006 | 1 | 0.4771 |
49 | 1-Feb-06 | 2006 | 2 | 0.90218 |
49 | 1-Mar-06 | 2006 | 3 | 0.97459 |
49 | 1-Apr-06 | 2006 | 4 | 0.42076 |
49 | 1-May-06 | 2006 | 5 | -2.65363 |
49 | 1-Jun-06 | 2006 | 6 | -0.46042 |
49 | 1-Jul-06 | 2006 | 7 | 0.2505 |
49 | 1-Aug-06 | 2006 | 8 | 2.3988 |
49 | 1-Sep-06 | 2006 | 9 | 2.42167 |
49 | 1-Oct-06 | 2006 | 10 | 2.24773 |
49 | 1-Nov-06 | 2006 | 11 | 0.98223 |
49 | 1-Dec-06 | 2006 | 12 | 0.81832 |
80 | 1-Jan-06 | 2006 | 1 | 3.29777 |
80 | 1-Feb-06 | 2006 | 2 | -0.37559 |
80 | 1-Mar-06 | 2006 | 3 | 0.94251 |
80 | 1-Apr-06 | 2006 | 4 | 1.12045 |
80 | 1-May-06 | 2006 | 5 | -2.67775 |
80 | 1-Jun-06 | 2006 | 6 | 0.09488 |
80 | 1-Jul-06 | 2006 | 7 | 0.85308 |
80 | 1-Aug-06 | 2006 | 8 | 2.25564 |
80 | 1-Sep-06 | 2006 | 9 | 1.10294 |
80 | 1-Oct-06 | 2006 | 10 | 1.90909 |
80 | 1-Nov-06 | 2006 | 11 | 2.05174 |
80 | 1-Dec-06 | 2006 | 12 | 1.07457 |
80 | 1-Jan-07 | 2007 | 1 | 0.97345 |
80 | 1-Feb-07 | 2007 | 2 | 0.35057 |
Hi @doylejm By any chances are you asking for this?
data have;
input ID date :date9. year month ret;
format date date9.;
cards;
49 1-Nov-05 2005 11 2.65188
49 1-Dec-05 2005 12 0.0682
49 1-Jan-06 2006 1 0.4771
49 1-Feb-06 2006 2 0.90218
49 1-Mar-06 2006 3 0.97459
49 1-Apr-06 2006 4 0.42076
49 1-May-06 2006 5 -2.65363
49 1-Jun-06 2006 6 -0.46042
49 1-Jul-06 2006 7 0.2505
49 1-Aug-06 2006 8 2.3988
49 1-Sep-06 2006 9 2.42167
49 1-Oct-06 2006 10 2.24773
49 1-Nov-06 2006 11 0.98223
49 1-Dec-06 2006 12 0.81832
80 1-Jan-06 2006 1 3.29777
80 1-Feb-06 2006 2 -0.37559
80 1-Mar-06 2006 3 0.94251
80 1-Apr-06 2006 4 1.12045
80 1-May-06 2006 5 -2.67775
80 1-Jun-06 2006 6 0.09488
80 1-Jul-06 2006 7 0.85308
80 1-Aug-06 2006 8 2.25564
80 1-Sep-06 2006 9 1.10294
80 1-Oct-06 2006 10 1.90909
80 1-Nov-06 2006 11 2.05174
80 1-Dec-06 2006 12 1.07457
80 1-Jan-07 2007 1 0.97345
80 1-Feb-07 2007 2 0.35057
;
proc sql;
create table want as
select *
from have
group by id ,year
having n(month)=12
order by id, date;
quit;
Hello @doylejm Can you please mark as answered and close the thread when you can?Thank you!
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.