proc sql;
create table have1 as
select sum(n) as n, *
from have
group by id, year,half, g;
create table have2 as
select distinct *
from have1
order by year,half,id, g ;
quit;
data have2;
set have2;
t=year*10+half*5;
run;
proc transpose data=have2 out=trans(drop=_name_);
by t id;
id g;
var n;
run;
proc sql;
select distinct id, count(distinct id) into :ids separated by ' ', :idn
from trans
quit;
ods output nlevels=dummy;
proc freq data=trans levels;
run;
%macro want();
%do id=1 %to &idn;
%let idk=%scan(&ids,&id,%str( ));
%put &idk;
proc sql;
select distinct t, count(distinct t) into :ts separated by ' ', :tn
from trans
where id=&idk;
quit;
%do t=1 %to &tn;
%let tk=%scan(&ts,&t,%str( ));
ods select nlevels;
ods output nlevels=nlev&id&t;
proc freq data=trans(where=(id=&idk and t=&tk) ) levels;
run;
ods output close;
proc sql noprint;
select strip(tablevar) ,strip(tablevar) into :vars separated by ' ', :varsd separated by ','
from nlev&id&t
where nnonmisslevels ne 0 and tablevar not in ('id','t');
quit;
%let nvar=%sysfunc(countw(&vars,' '));
data factor1(keep=&vars id t %do j=1 %to &nvar;
%let k=%scan(&vars,&j,%str( ));
rename=(&k=_&k)
%end;);
set trans;
%do j=1 %to &nvar;
%let k=%scan(&vars,&j,%str( ));
if missing(&k) then &k=0;
if t=&tk then &k=-(&k);
%end;
where (id eq &idk and &tk<=t<=%eval(&tk+5));
run;
proc sql;
create table factor2 as
select %do j=1 %to &nvar;%let k=%scan(&vars,&j,%str( ));
abs(sum(_&k)) as _&k, %end; id
from factor1
group by id;
quit;
data factor(drop=id);
set factor2;
t=&tk; output;
t=%eval(&tk+5); output;
run;
data t(keep=&vars id t);
merge trans(in=in where=(id ne &idk and &tk<=t<=%eval(&tk+5))) factor;
by t;
%do j=1 %to &nvar;
%let k=%scan(&vars,&j,%str( ));
if missing(&k) then &k=0;
if t=&tk then &k=-(&k);
&k=&k/_&k;
%end;
if in;
run;
proc sql;
select count(distinct t) into :maxt
from t;
quit;
%if &maxt eq 2 %then %do;
proc sql;
create table t&t as
select sum(sum(&varsd)) as p,&idk as id,%eval(&tk+5) as t
from t;
quit;
%end;
%else %do;
data t&t;
run;
%end;
%end;
data tid&id;
set %do t=1 %to &tn; t&t %end;;
run;
%end;
data tall;
set %do id=1 %to &idn; tid&id %end;;
year=floor(t/10)-(mod(t,10)=0);
half=mod(t/5,2)+(mod(t/5,2)=0)*2;
run;
proc sort data=have;
by id year half;
run;
proc sort data=tall;
by id year half;
run;
data want;
merge have(in=a) tall(drop=t);
by id year half;
if a;
run;
%mend;
%want;