Question : add the number of record from dates and transpose data

[mahdibouallegue]

I have a big data like this

data have;

input ID$ Parity daterecord milk;

datalines;

                      B98        1   17/08/2001 30.8

                      B98        1   28/09/2001 22.0

                      B98        1   12/11/2001 27.8

                      B98        1   26/12/2001 25.8

                      B98        1   06/02/2002 33.0

                      B98        1   23/03/2002 30.4

                      B98        1   04/05/2002 18.4

                      B98        1   14/06/2002 17.4

                      B98        1   29/07/2002 11.2

                      B98        1   09/09/2002   .

                      B98        2   21/10/2002 40.0

                      B98        2   02/12/2002 29.2

                      B98        2   15/01/2003 32.6

                      B98        2   26/02/2003 31.2

                      B98        2   07/04/2003 13.4

                      B98        2   21/05/2003 26.6

                      B98        2   21/05/2003 26.6

                      B98        2   11/08/2003 25.8

                      B98        2   19/09/2003 15.9

                      B98        2   31/10/2003  .

                      B98        3   16/12/2003 45.5

                      B98        3   30/01/2004 33.9

                      B98        3   12/03/2004 28.1

                      B98        3   26/04/2004 25.9

                      B98        3   07/06/2004 17.2

                      B98        3   19/07/2004 20.2

                      B98        3   01/09/2004 16.1

                       B09        1   28/09/2001 16.4

                      B09        1   12/11/2001 18.4

                      B09        1   26/12/2001 26.8

                      B09        1   29/07/2002  .

                      B09        1   09/09/2002  .

                      B09        2   21/10/2002 21.2

                      B09        2   02/12/2002 22.8

                      B09        2   15/01/2003 24.2

                      B09        3   12/03/2004 47.3

                      B09        3   26/04/2004 40.8

                      B09        3   07/06/2004 34.8

                      B09        3   19/07/2004 30.0

                      B09        3   01/09/2004 29.7

                      B09        4   08/01/2005 23.8

                      B09        4   21/02/2005 38.5

;

run;

I Want add a new variable that definte the number of record in each date with parity. I want a new data set like this

data new;

input ID$ Parity daterecord record number milk;

datalines;

   B98        1       17/08/2001 1   30.8

                      B98        1       28/09/2001 2  22.0

                      B98        1       12/11/2001 3  27.8

                      B98        1       26/12/2001 4   25.8

                      B98        1       06/02/2002 5   33.0

                      B98        1       23/03/2002 6   30.4

                      B98        1       04/05/2002 7   18.4

                      B98        1       14/06/2002 8  17.4

                      B98        1       29/07/2002 9   11.2

                      B98        1       09/09/2002 10    .

                      B98        2       21/10/2002 1   40.0

                      B98        2       02/12/2002 2   29.2

                      B98        2       15/01/2003 3   32.6

                      B98        2       26/02/2003 4   31.2

                      B98        2       07/04/2003 5   13.4

                      B98        2       21/05/2003 6   26.6

                      B98        2       01/07/2003 7   22.2

                      B98        2       11/08/2003 8   25.8

                      B98        2       19/09/2003 9   15.9

                      B98        2       31/10/2003 10   .

                      B98        3       16/12/2003 1   45.5

                      B98        3       30/01/2004 2   33.9

                      B98        3       12/03/2004 3   28.1

                      B98        3       26/04/2004 4   25.9

                      B98        3       07/06/2004 5   17.2

                      B98        3       19/07/2004 6   20.2

                      B98        3       01/09/2004 7   16.1

   B09        1       28/09/2001 1   16.4

                      B09        1       12/11/2001 2   18.4

                      B09        1       26/12/2001 3   26.8

                      B09        1       29/07/2002 5   .

                      B09        1       09/09/2002 6  .

                      B09        2       21/10/2002 1   21.2

                      B09        2       02/12/2002 2   22.8

                      B09        2       15/01/2003 3   24.2

                      B09        3       12/03/2004 1   47.3

                      B09        3       26/04/2004 2   40.8

                      B09        3       07/06/2004 3   34.8

                      B09        3       19/07/2004 4   30.0

                      B09        3       01/09/2004 5   29.7

                      B09        4       08/01/2005 1   23.8

                      B09        4       21/02/2005 2   38.5

; run;

Then I wante to transpose data like this

data have;

input ID$ Parity daterecord1 milk1 daterecord2 milk2 daterecord3 milk3 daterecord4 milk4 daterecord5 milk5 daterecord6 milk6 daterecord7 milk7 daterecord8 milk8 daterecord9 milk9 daterecord10 milk10;

datalines;

   B98        1   17/08/2001 30.8 28/09/2001 22.0 12/11/2001 27.8 26/12/2001 25.8 06/02/2002 33.0 23/03/2002 30.4 04/05/2002 18.4 14/06/2002 17.4 29/07/2002 11.2  1   09/09/2002 .

   B98        2   21/10/2002 40.0 02/12/2002 29.2 15/01/2003 32.6 26/02/2003 31.2 07/04/2003 13.4 21/05/2003 26.6 21/05/2003 26.6 11/08/2003 25.8 19/09/2003 15.9  2   31/10/2003 .

   ......

Thank you for helping

;

[Ksharp · Accepted solution]

OR use MERGE Skill:

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

Code: Program

data have;
input ID $ Parity daterecord : $40. milk;
datalines;
  B98 1 17/08/2001 30.8
  B98 1 28/09/2001 22.0
  B98 1 12/11/2001 27.8
  B98 1 26/12/2001 25.8
  B98 1 06/02/2002 33.0
  B98 1 23/03/2002 30.4
  B98 1 04/05/2002 18.4
  B98 1 14/06/2002 17.4
  B98 1 29/07/2002 11.2
  B98 1 09/09/2002 .
  B98 2 21/10/2002 40.0
  B98 2 02/12/2002 29.2
  B98 2 15/01/2003 32.6
  B98 2 26/02/2003 31.2
  B98 2 07/04/2003 13.4
  B98 2 21/05/2003 26.6
  B98 2 21/05/2003 26.6
  B98 2 11/08/2003 25.8
  B98 2 19/09/2003 15.9
  B98 2 31/10/2003 .
  B98 3 16/12/2003 45.5
  B98 3 30/01/2004 33.9
  B98 3 12/03/2004 28.1
  B98 3 26/04/2004 25.9
  B98 3 07/06/2004 17.2
  B98 3 19/07/2004 20.2
  B98 3 01/09/2004 16.1
  B09 1 28/09/2001 16.4
  B09 1 12/11/2001 18.4
  B09 1 26/12/2001 26.8
  B09 1 29/07/2002 .
  B09 1 09/09/2002 .
  B09 2 21/10/2002 21.2
  B09 2 02/12/2002 22.8
  B09 2 15/01/2003 24.2
  B09 3 12/03/2004 47.3
  B09 3 26/04/2004 40.8
  B09 3 07/06/2004 34.8
  B09 3 19/07/2004 30.0
  B09 3 01/09/2004 29.7
  B09 4 08/01/2005 23.8
  B09 4 21/02/2005 38.5
;
run;
proc sql;
 select max(count) into : m separated by ' '
  from (select count(*) as count from have group by id,Parity );
quit;
proc summary data=have nway;
 class id Parity ;
 output out=want(drop=_:) idgroup(out[&m]  (daterecord milk)=);
run;

[mahdibouallegue]

Mr Xia

Thank you for your interest and help. for transposed data with the proposed proc summary my problem is resolved. but for the control number for recorded date and for each parity I can't added the variable control number with proc sql, I tainning it again. thank you for the interested paper.

[Ksharp]

I don't understand what you mean. What is your control number ? Why would you add it ?

[mahdibouallegue]

Hello,

Thank you again

example for ID=98

                     ID$      Parity      daterecord      milk;

                      B98        1        17/08/2001      30.8

                      B98        1        28/09/2001      22.0

                      B98        1        12/11/2001      27.8

                     B98        2        21/10/2002      40.0

                      B98        2        02/12/2002      29.2

                      B98        2        15/01/2003      32.6

                      B98        2        26/02/2003      31.2

                     B98        3        16/12/2003      45.5

                      B98        3   30/01/2004           33.9

                      B98        3   12/03/2004           28.1

   For    the first parity:

For the daterecord = 17/08 /2001 that is the first control of milk =30.8 and control test or number=1

For the daterecord = 28/09 /2001 that is the second control of milk =22 and control test or number=2

For the daterecord = 12/011/2001 that is the third control of milk =27.8 and control test or number=3.......ect

For the same ID and for the seconde parity each date of record we add the number of the control     

the new data set it look like :

                     ID      Parity      daterecord      record number      milk;

                      B98       1       17/08/2001           1                       30.8

                      B98        1       28/09/2001          2                      22.0

                      B98        1       12/11/2001          3                      27.8

                      B98       2       21/10/2002           1                          40.0

                      B98        2       02/12/2002           2                       29.2

                      B98        2       15/01/2003           3                       32.6

                      B98        2       26/02/2003           4                       31.2

                      B98        3       16/12/2003           1                       45.5

                      B98        3       30/01/2004           2                       33.9

                      B98        3       12/03/2004           3                       28.1

[Ksharp]

You don't need add recode_number into SQL. If you want transpose it as milk did . Try this one :

Code: Program

data have;
input ID $ Parity daterecord : $40.  record_number milk;
datalines;
  B98 1 17/08/2001 1 30.8
  B98 1 28/09/2001 2 22.0
  B98 1 12/11/2001 3 27.8
  B98 2 21/10/2002 1 40.0
  B98 2 02/12/2002 2 29.2
  B98 2 15/01/2003 3 32.6
  B98 2 26/02/2003 4 31.2
  B98 3 16/12/2003 1 45.5
  B98 3 30/01/2004 2 33.9
  B98 3 12/03/2004 3 28.1
;
run;
proc sql;
 select max(count) into : m separated by ' '
  from (select count(*) as count from have group by id,Parity );
quit;
proc summary data=have nway;
 class id Parity ;
 output out=want(drop=_:) idgroup(out[&m]  (daterecord record_number  milk)=);
run;

[mahdibouallegue]

Thank you very much

I mean by control number the identity number as indicated in your paper

[Ksharp]

You mean how to creating such identity number ?

data have;

set have;

by id;

if first.id then identity_number=0;

identity_number+1;

run;