data have;
infile datalines truncover dlm="|" dsd;
input id :$20. (start_date end_date) (:date9.) (attribute_1 - attribute_4) ($);
format start_date end_date date9.;
datalines;
ID1|01MAR2014|31DEC9999|BIG|YES||
ID2|01SEP2015|30NOV2020|||TWO|
ID2|01SEP2015|31DEC9999|SMALL|||
ID2|01AUG2021|31DEC9999|||TWO|
ID3|01DEC2014|31MAY2016||YES||
ID3|01DEC2014|29JUN2017||||OK
ID3|01DEC2014|31DEC9999|MEDIUM|||
ID3|31MAR2015|29SEP2017|||ONE|
ID3|30JUN2017|31DEC9999||YES||TBD
ID3|30SEP2017|31DEC9999|||ONE, TWO|
;
data step1;
set have;
length attrib $ 80;
attrib=catx('|',of attribute_1-attribute_4);
do date=start_date to end_date;
output;
end;
format date date9.;
drop start_date end_date attribute_1 - attribute_4;
run;
proc sort data=step1; by id date; run;
data step2;
n=0;
do until(last.date);
set step1;
by id date ;
array x{20} $ 20 ;
length unique $ 200;
do i=1 to countw(attrib,'|');
temp=scan(attrib,i,'|');
if temp not in x then do;n+1;x{n}=temp;end;
end;
end;
call sortc(of x{i});
unique=catx("|",of x{*});
drop i temp attrib x: n;
run;
data step3;
do until(last.unique);
set step2;
by id unique notsorted;
if first.unique then start=date;
end;
end=date;
drop date;
format start end date9.;
run;
data step4;
if _n_=1 then do;
if 0 then set have;
declare hash h1(dataset:'have(keep=attribute_1 where=(attribute_1 is not missing))');
h1.definekey('attribute_1');
h1.definedone();
declare hash h2(dataset:'have(keep=attribute_2 where=(attribute_2 is not missing))');
h2.definekey('attribute_2');
h2.definedone();
declare hash h3(dataset:'have(keep=attribute_3 where=(attribute_3 is not missing))');
h3.definekey('attribute_3');
h3.definedone();
declare hash h4(dataset:'have(keep=attribute_4 where=(attribute_4 is not missing))');
h4.definekey('attribute_4');
h4.definedone();
end;
set step3;
length key $ 20;
do i=1 to countw(unique,'|');
key=scan(unique,i,'|');
if h1.check(key:key)=0 then n=1;
if h2.check(key:key)=0 then n=2;
if h3.check(key:key)=0 then n=3;
if h4.check(key:key)=0 then n=4;
output;
end;
drop unique i start_date end_date attribute_:;
run;
proc transpose data=step4 out=want(drop=_name_) prefix=attribute_;
by id start end;
var key;
id n;
run;
... View more