sas - Transpose rows to columns -

my input data:

preferred output data:

my best try:

which wrong because includes idnumber 2 , 4.

data:

    data work.transpose_csv; length     idnumber           8     start_end        $ 5     date               8 ; format     idnumber         best1.     start_end        $char5.     date             yymmdd10. ; informat     idnumber         best1.     start_end        $char5.     date             yymmdd10. ; input     idnumber         : ?? best1.     start_end        : $char5.     date             : ?? yymmdd10. ; datalines; 2 start 1994-05-01 2 end 1996-11-04 4 start 1979-07-18 5 start 2005-02-01 5 end 2009-09-17 5 start 2010-10-01 5 end 2012-10-06 ; run; 

my best try:

    proc transpose data=transpose_csv                    out =wide;                    idnumber;                    id start_end ;     run; 

as shown post can done in r, need in sas: spread duplicate identifiers (using tidyverse , %>%)

the problem proc transpose here can have multiple events particular idnumber. if able change source data add id variable, e.g. event_id, make task easier.

you can either continue proc transpose below, followed data step bring start / end dates on 1 row, or in single data step , hard code values. there other methods well, such hash solution work type of problem.

edit : added 3rd method first creates event_id, makes subsequent proc transpose easy

/* source data */ data work.transpose_csv; length     idnumber           8     start_end        $ 5     date               8 ; format     idnumber         best1.     start_end        $char5.     date             yymmdd10. ; informat     idnumber         best1.     start_end        $char5.     date             yymmdd10. ; input     idnumber         : ?? best1.     start_end        : $char5.     date             : ?? yymmdd10. ; datalines; 2 start 1994-05-01 2 end 1996-11-04 4 start 1979-07-18 5 start 2005-02-01 5 end 2009-09-17 5 start 2010-10-01 5 end 2012-10-06 ; run;  /* method1 */ proc transpose data=transpose_csv                out =wide1 (drop=_: start_end);                idnumber start_end notsorted;                id start_end ; run;  data wide2; set wide1; idnumber; retain _start; if not missing(start) _start=start; if not missing(end) or last.idnumber do;         start=_start;         output;         end; drop _start; run;   /* method2 */ data wide3; set transpose_csv; idnumber; retain start; format start end yymmdd10.; if start_end='start' start=date; if start_end='end' do;     end=date;     output;     end; else if last.idnumber output; drop start_end date; run;  /* method3 */ data transpose_csv1; set transpose_csv; idnumber; if first.idnumber event_id=0; event_id+(start_end='start'); run;  proc transpose data=transpose_csv1                    out =wide4 (drop=_: event_id);                    idnumber event_id;                    id start_end ;    run;