In order to pick the correct variable which matches with a start date you need to know exactly how these variables have been created and use the same algorithm. There are some challenges in that like below example illustrates.
proc format;
picture yyww (default=4)
other='%0y%0V' (datatype=date)
;
quit;
data test;
format date date9.;
date='01jan2001'd;
YearWeek=put(date,yyww.);
week=week(date);
output;
date='01jan2012'd;
YearWeek=put(date,yyww.);
week=week(date);
output;
date='30dec2012'd;
YearWeek=put(date,yyww.);
week=week(date);
output;
run;
Below example code where I've made up the year and week calculation. The hardedst bit was to create some sample data.
Step 1: Create sample data
data have;
length id 8 yw $4 var 8;
format StartDate date9.;
format _date date9.;
do id=1 to 100;
StartDate=floor(ranuni(1)*2000) +'01jan2005'd;
_date=StartDate;
do while(_date<='31Dec2015'd);
yw=put(_date,year2.)||put(ceil((_date-intnx('year',_date,-1,'e'))/7),z2.);
var=ceil(ranuni(1)*14);
if var>5 then call missing(var);
output;
_date=_date+7;
end;
end;
run;
proc transpose data=have out=source(drop=_:) prefix=c;
by id;
id yw;
var var;
run;
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where libname='WORK' and memname='SOURCE' and upcase(name) like 'C%'
order by name
;
quit;
data source;
length id StartDate &varlist 8;
format StartDate date9.;
merge have(keep=id StartDate) source;
by id;
if first.id;
run;
Step 2: Count 1,2,3.
The important parts in the code:
- Variables in array must be in sorted order (from earliest to latest "date").
- The hash holds the number of the array element - key is the variable name
- The algorithm used to calculate the starting variable name based on start date must use the same logic than what has been used to create the variables
data want(drop=_:);
length Count123 8;
set source;
array vars {*} &varlist;
if _n_=1 then
do;
length _varname $32 _arr_element 8;
dcl hash h();
_rc=h.defineKey('_varname');
_rc=h.defineData('_arr_element');
_rc=h.defineDone();
do _arr_element=1 to dim(vars);
_varname=upcase(vname(vars[_arr_element]));
_rc=h.add();
end;
end;
if missing(StartDate) then _arr_element=1;
else
do;
_varname='C'||put(StartDate,year2.)||put(ceil((StartDate-intnx('year',StartDate,-1,'e'))/7),z2.);
_rc=h.find();
end;
do _i=_arr_element to dim(vars);
if vars[_i] in (1,2,3) then Count123=sum(Count123,1);
end;
run;
... View more
