OK. Maybe I understand what you mean now. Try this one :
data person;
input Person_ID $ Plan_ID $ Age_group $ Type $ Timepoint Payment Result;
datalines;
901 101 10to20 A 0 500 0
901 119 10to20 A 1 400 1
901 129 10to20 A 3 250 0
905 203 21to30 B 0 100 0
905 287 21to30 B 1 150 0
905 301 21to30 B 2 100 1
910 401 21to30 B 0 900 0
910 450 21to30 B 1 800 1
910 500 21to30 B 2 100 1
910 600 21to30 B 3 150 1
920 700 10to20 A 0 300 1
920 800 10to20 A 1 100 1
;
proc sql;
create table level as
select distinct Person_ID,Age_group , Type , Timepoint
from person
order by 1,2,3,4;
quit;
data level1;
set level;
lag_Timepoint=lag(Timepoint);
if Person_ID=lag(Person_ID) and Age_group=lag(Age_group) and Type=lag(Type) and dif(Timepoint)=1 then do;
first=lag_Timepoint; last=Timepoint;output;
end;
drop lag_Timepoint Timepoint;
run;
proc sql;
create table level2 as
select distinct Age_group , Type , first,last
from level1;
quit;
%macro report(Age_group=,Type=,first=,last=);
data key;
set level1;
if Age_group="&Age_group." and Type="&Type." and first=&first. and last=&last.;
run;
data key1;
set key;
Timepoint=first;output;
Timepoint=last;output;
drop first last;
run;
data have;
if _n_=1 then do;
if 0 then set key;
declare hash h(dataset:'key1');
h.definekey('Person_ID','Age_group','Type','Timepoint');
h.definedone();
end;
set person;
if h.check()=0;
run;
proc sql;
create table temp as
select "&Age_group." as Age_group length=80,"&Type." as Type length=80,
catx(' ',&first.,'to',&last.) as Time_period length=80,
(select count(distinct Person_ID) from have) as n_people,
(select count(distinct Person_ID) from have where Timepoint=&first. and result=1)/
(select count(distinct Person_ID) from have where Timepoint=&first. ) as prop_before_pay format=percent8.2,
(select count(distinct Person_ID) from have where Timepoint=&last. and result=1)/
(select count(distinct Person_ID) from have where Timepoint=&last. ) as prop_after_pay format=percent8.2,
(select sum(payment) from have where Timepoint=&first.) as Total_payment
from have(obs=1);
quit;
proc append base=want data=temp force;run;
run;
%mend;
proc delete data=want;run;
data _null_;
set level2;
call execute(catt('%report(Age_group=',Age_group,',Type=',Type,',first=',first,',last=',last,')'));
run;
... View more