topic Re: insert the row observations between the first and last observations in SAS Data Management

insert the row observations between the first and last observations

sas_user_k — Thu, 29 Dec 2016 03:40:14 GMT

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

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

Jagadishkatam — Thu, 29 Dec 2016 03:54:32 GMT

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.

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

sas_user_k — Thu, 29 Dec 2016 03:59:43 GMT

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

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

Ksharp — Thu, 29 Dec 2016 04:10:09 GMT

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;

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

sas_user_k — Thu, 29 Dec 2016 04:27:35 GMT

Thank you so much! The code you suggested works!

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

Jagadishkatam — Thu, 29 Dec 2016 06:39:39 GMT

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;