Transposing a dataset by criteria

[Sean_OConnor]

Folks,

Could anyone think of a way to go from A to B. 

Would like to carry out a transpose but would be transposed by the oldest person in the house.

Have


ID	House_id	DOB
024978P	602A7C469ADDA38E96A	01-Aug-84
0565758T	602A7C469ADDA38E96A	01-Jan-05
09363467P	602A7C469ADDA38E96A	01-Feb-06
048837O	602A7C469ADDA38E96A	01-Mar-12
035485W	9D2C6FCAB9F90027A	01-Jun-48
0614701F	F2D053348CF80DE494D650	01-Mar-45
08088430X	F2D053348CF80DE494D650	01-Nov-51

WANT

ID	ID_1	DOB	DOB_1	House_ID
024978P	0565758T	01-Aug-84	01-Jan-05	602A7C469ADDA38E96A
024978P	09363467P	01-Aug-84	01-Feb-06	602A7C469ADDA38E96A
048837O		01-Mar-12		602A7C469ADDA38E96A
0614701F	08088430X	01-Mar-45	01-Nov-51	F2D053348CF80DE494D650

[RW9]

Use arrays, you can check the maximum iterations before, for my code I just assumed 1:

data want;
  set have;
  by house_id;
  array id_{1} $200;
  array dob_{1} $200;
  if not(first.house_id) then do;
    id_{1}=id;
    dob_{1}=dob;
  end;
  if last.house_id then output;
run;

Of course you will likely have a lot more than 1, so you would need to calculate how many and not hardcode in.  If you do only have one, then just retain the two extra variables.  You could also just do two proc transposes and merge them back on.

[Astounding]

I think I understand the intent here.  To begin, make sure the data is properly sorted:

proc sort data=have;

by House_ID DOB;

run;

Since you are getting pairs of observations, a DATA Step (with some renaming) would be an appropriate tool:

data want;

set have;

by House_ID;

if first.House_ID then do;

  id_0 = id;

   dob_0 = dob;

   if last.House_ID then output;

end;

else do;

   id_1 = id;

   dob_1 = dob;

   output;

end;

retain id_0 dob_0;

drop id dob;

run;