topic Re: Question : add the number of record from dates and transpose data in SAS Programming

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

mahdibouallegue — Mon, 20 Jul 2015 13:54:20 GMT

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 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

;

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

Ksharp — Mon, 20 Jul 2015 14:22:12 GMT

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;

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

mahdibouallegue — Tue, 28 Jul 2015 02:39:18 GMT

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.

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

Ksharp — Tue, 28 Jul 2015 14:22:09 GMT

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

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

mahdibouallegue — Tue, 28 Jul 2015 23:08:56 GMT

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

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

Ksharp — Wed, 29 Jul 2015 12:59:36 GMT

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;

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

mahdibouallegue — Thu, 30 Jul 2015 13:14:30 GMT

Thank you very much

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

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

Ksharp — Sat, 01 Aug 2015 13:23:16 GMT

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;