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.
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 |
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;
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
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.