topic Re: how to subset data: monthly data but only when all 12 months in a year are available. in SAS Programming

how to subset data: monthly data but only when all 12 months in a year are available.

doylejm — Wed, 15 May 2019 18:23:36 GMT

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

Re: how to subset data: monthly data but only when all 12 months in a year are available.

novinosrin — Wed, 15 May 2019 18:29:01 GMT

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;

Re: how to subset data: monthly data but only when all 12 months in a year are available.

doylejm — Wed, 15 May 2019 19:47:54 GMT

Thank you very much.

Re: how to subset data: monthly data but only when all 12 months in a year are available.

novinosrin — Fri, 17 May 2019 03:43:31 GMT

Hello @doylejm Can you please mark as answered and close the thread when you can?Thank you!