SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

insert the row observations between the first and last observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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
1120101
1120102
1120103
1120104
1120111
1120112
1120113
1120114
1120121
1120122
1120123
1219944
1219951
1219952
1219953
1320101
1320102
2120001
2120002
2120003
2120004
2120011
2120012
2120013
2120014
2120021
2120022
2220043
2220044
2220051
2220052
2220053
2220054
2220061
2220062
3120034
3120041
3120042
3120043
3120044
3219934
3219941
3219942
3219943
3219944
3219951
3219952

 


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

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

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: 9,681

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!
Trusted Advisor
Posts: 1,128

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 355 views
  • 3 likes
  • 3 in conversation