There is no OOTB syntax in SQL for this.
But you can use other logic to make it more dynamic.
My go-to for this scenario would a PROC TRANSPOSE, fixing a proper sas date value and the sum using SQL.
data have;
input study_id $ exp $ group $ mon1 mon2 mon3 mon4 mon5 mon6;
rowid = _n_;
datalines;
S001 exposed a 1 1 0 0 0 0
S001 exposed a 0 0 1 1 0 0
S001 exposed a 0 0 0 0 1 0
S002 exposed b 1 0 0 0 0 0
S002 exposed b 0 1 1 0 0 0
S002 unexposed c 0 0 0 1 1 1
S003 unexposed c 1 1 1 1 1 1
S004 unexposed c 1 1 1 0 0 0
S004 unexposed d 0 0 0 1 1 1
;
RUN;
proc transpose data=have
out=want1(rename=(col1=present))
name=month;
by study_id exp group rowid;
var mon1-mon6;
run;
%let startmon=01jan2010;
data want2;
set want1;
month_num = intnx('MONTH',"&startMon"d,input(substr(month,4),2.));
format month_num monyy.;
run;
proc sql;
create table want3 as
select study_id, exp, group, month_num, sum(present) as sumpres
from want2
group by 1, 2, 3, 4
;
quit;
... View more
