@cx2019 wrote:
As a new user, I have a problem. Then I discribe it in brief.
data t1;
input id $;
cards;
A20152017
B20162018
C20152018
;
run;
data t2;
input year A B C;
cards;
2014 1 1 1
2015 2 1 3
2016 1 1 2
2017 3 2 3
2018 1 2 1
2019 2 3 3
;
run;
I have 2 tables, t1 and t2. In t1, the first charactor(A, B and C) of id is name, the second to the fifth and the sixth to the ninth mean starting year and ending year respectively. In t2, there are points in these years for A, B and C.
I want to get the average points during the years shown in t1 and merge them with set1.
Are you receiving the data in this bizarre format? If so you have no choice but to work with in this format. Or are you creating the data in this format? (in which case, don't do it like this, there are much better formats).
With the data as is, some re-arranging of the data will be needed.
data t1;
input id $10.;
startyear=input(substr(id,2,4),4.);
endyear=input(substr(id,6,5),4.);
id=substr(id,1,1);
cards;
A20152017
B20162018
C20152018
;
run;
data t2;
input year A B C;
value=a; id='A'; output;
value=b; id='B'; output;
value=c; id='C'; output;
drop a b c;
cards;
2014 1 1 1
2015 2 1 3
2016 1 1 2
2017 3 2 3
2018 1 2 1
2019 2 3 3
;
run;
proc sql;
create table t3 as select t2.id,mean(t2.value) as mean from t2
left join t1 on t1.id=t2.id
where t2.year>=t1.startyear and t2.year<=t1.endyear
group by t2.id;
quit;
--
Paige Miller
