BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas_user_k
Obsidian | Level 7

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16
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
sas_user_k
Obsidian | Level 7

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
Super User
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
Obsidian | Level 7
Thank you so much! The code you suggested works!
Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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