## insert the row observations between the first and last observations

Solved
Occasional Contributor
Posts: 19

# insert the row observations between the first and last observations

[ Edited ]

``````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

Accepted Solutions
Solution
‎12-28-2016 11:27 PM
Super User
Posts: 10,415

## Re: insert the row observations between the first and last observations

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

All Replies
Posts: 1,140

## Re: insert the row observations between the first and last observations

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.
Thanks,
Jag
Occasional Contributor
Posts: 19

## Re: insert the row observations between the first and last observations

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

Solution
‎12-28-2016 11:27 PM
Super User
Posts: 10,415

## Re: insert the row observations between the first and last observations

``````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;
``````
Occasional Contributor
Posts: 19

## Re: insert the row observations between the first and last observations

Thank you so much! The code you suggested works!
Posts: 1,140

## Re: insert the row observations between the first and last observations

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;``````
Thanks,
Jag
☑ This topic is solved.