insert the row observations between the first and last observations

[sas_user_k]

data have;
input id1 id2 year qtr;
cards;
1 1 2010 1
1 1 2012 3
1 2 1994 4
1 2 1995 3
1 3 2010 1
1 3 2010 2
2 1 2000 1
2 1 2002 2
2 2 2004 3
2 2 2006 2
3 1 2003 4
3 1 2004 4
3 2 1993 4
3 2 1995 2
;
run;

Hi. I would like to insert the observations between the first year-quarter row and the last year-quarter row grouped by id1 and id2 as shown above, and the resulting output should look like shown below. Do you have any idea on how to easily do this? Thank you.

id1        id2        year       qtr

1	1	2010	1
1	1	2010	2
1	1	2010	3
1	1	2010	4
1	1	2011	1
1	1	2011	2
1	1	2011	3
1	1	2011	4
1	1	2012	1
1	1	2012	2
1	1	2012	3
1	2	1994	4
1	2	1995	1
1	2	1995	2
1	2	1995	3
1	3	2010	1
1	3	2010	2
2	1	2000	1
2	1	2000	2
2	1	2000	3
2	1	2000	4
2	1	2001	1
2	1	2001	2
2	1	2001	3
2	1	2001	4
2	1	2002	1
2	1	2002	2
2	2	2004	3
2	2	2004	4
2	2	2005	1
2	2	2005	2
2	2	2005	3
2	2	2005	4
2	2	2006	1
2	2	2006	2
3	1	2003	4
3	1	2004	1
3	1	2004	2
3	1	2004	3
3	1	2004	4
3	2	1993	4
3	2	1994	1
3	2	1994	2
3	2	1994	3
3	2	1994	4
3	2	1995	1
3	2	1995	2

[Jagadishkatam]

The question is not clear as i see in the expected output for id1=1 the 2010 years gets 4 records and also includes 2011 with 4 records but 2012 get only 3 records. Could you please provide more details.

[sas_user_k]

That's because, for id1=1 & id2=1 group, the first observation is Year 2010/Quarter 1 and the last observation is Year 2012/Quarter 3, so the inserting observations should be the ones from Year 2010/Quarter 2 through Year 2012/Quarter 2.

Year 2010/Quarter 2

Year 2010/Quarter 3

Year 2010/Quarter 4

Year 2011/Quarter 1

Year 2011/Quarter 2

Year 2011/Quarter 3

Year 2011/Quarter 4

Year 2012/Quarter 1

Year 2012/Quarter 2

[Ksharp · Accepted solution]

data have;
input id1 id2 year qtr;
date=yyq(year,qtr);
cards;
1 1 2010 1
1 1 2012 3
1 2 1994 4
1 2 1995 3
1 3 2010 1
1 3 2010 2
2 1 2000 1
2 1 2002 2
2 2 2004 3
2 2 2006 2
3 1 2003 4
3 1 2004 4
3 2 1993 4
3 2 1995 2
;
run;
data want;
 merge have have(firstobs=2 keep=id1 id2 date
 rename=(id1=_id1 id2=_id2 date=_date));
output;
if id1=_id1 and id2=_id2 then do;
 do i=1 to intck('qtr',date,_date)-1;
  temp=intnx('qtr',date,i);
  year=year(temp);qtr=qtr(temp);output;
 end;
end;
drop _: temp i date;
run;

[sas_user_k]

Thank you so much! The code you suggested works!

[Jagadishkatam]

alternatively

proc sort data=have;
by id1 id2 year qtr;
run;

proc expand data=have out=want(drop=year qtr) to=quarter;
id date;
by id1 id2;
run;

data want_;
set want;
year=year(date);
qtr=qtr(date);
run;