A double transpose can reshape the data into the form you desire. A double transpose is needed because two variables are being pivoted into columns.
data have;
attrib id length=8 startdate enddate informat=ddmmyy10. format=ddmmyy10.;
input ID Startdate Enddate; datalines;
1 01/01/2001 31/12/2001
1 01/01/2002 04/05/2002
1 06/07/2002 30/11/2002
2 05/05/2001 05/06/2001
2 07/07/2001 15/10/2001
3 01/04/2002 08/09/2003
;
data have_view / view=have_view;
set have;
by id;
if first.id then seq=1; else seq+1;
run;
proc transpose data=have_view out=name_value;
by id seq;
var startdate enddate;
run;
proc transpose data=name_value out=want(drop=_name_);
by id;
id _name_ seq;
run;
A second way is to perform an 'array-based transposed'. This requires an apriori scan of the data to find the size of the largest id group.
proc sql noprint;
select max (subselect.freq) into :across_count trimmed from
(select id, count(*) as freq from have group by id) as subselect;
%put NOTE: &=across_count;
data want2;
do _n_ = 1 by 1 until (last.id);
set have;
by id;
array starts startdate1-startdate&across_count;
array ends enddate1 -enddate&across_count;
starts [_n_] = startdate;
ends [_n_] = enddate;
end;
attrib startdate: enddate: format=ddmmyy10. informat=ddmmyy10.;
drop startdate enddate;
run;
If you want the array based solution to have a column order that is like start1, end1, start2, end2, etc... you will need a macro to generate the variable names in that order. Example:
%macro enum(min=1, max=, template=);
%local index;
%do index = &min %to &max;
%unquote(&template)
%end;
%mend;
data want3;
do _n_ = 1 by 1 until (last.id);
set have;
by id;
attrib
%enum(max=&across_count, template=%nrstr(startdate&index enddate&index))
format=ddmmyy10. informat=ddmmyy10.
;
array starts startdate1-startdate&across_count;
array ends enddate1 -enddate&across_count;
starts [_n_] = startdate;
ends [_n_] = enddate;
end;
drop startdate enddate;
run;
... View more