topic Re: comparing rows in a dataset in SAS Programming

comparing rows in a dataset

Anita_n — Wed, 17 Feb 2021 08:59:05 GMT

Dear all,

I have a dataset that looks like this

pid tum_typ sex date_of_diagnosis

1 2 2 10/04/2015

1 2 2 01/06/2010

1 2 2 11/03/2019

2 2 1 09/09/2000

2 2 1 05/08/2018

3 2 1 02/10/2020

4 2 2 11/02/2001

4 2 2 15/12/1998

5 2 1

5 2 1 07/07/2020

5 2 1 06/01/2020

I wish to create a new variable called order_of_tum which indicates the 1., 2. 3., etc of tumor a patient has grouped by id and date. The result should look like this :

That means I compare the first and second row and if date 1 is less than date2 then date1 =1. and date2=2.

pid tum_typ sex date_of_diagnosis order_of_tum

1 2 2 01/06/2010 1

1 2 2 10/04/2015 2

1 2 2 11/03/2019 3

2 2 1 09/09/2000 1

2 2 1 05/08/2018 2

3 2 1 02/10/2020 1

4 2 2 15/12/1998 1

4 2 2 11/02/2001 2

5 2 1 06/01/2020 1

5 2 1 07/07/2020 2

5 2 1 3

Thanks

Re: comparing rows in a dataset

PeterClemmensen — Wed, 17 Feb 2021 09:10:52 GMT

I think this is what you want

data have;
infile datalines missover;
input pid tum_typ sex date_of_diagnosis : ddmmyy10.;
format date_of_diagnosis ddmmyy10.;
datalines;
1 2 2 10/04/2015
1 2 2 01/06/2010
1 2 2 11/03/2019
2 2 1 09/09/2000
2 2 1 05/08/2018
3 2 1 02/10/2020
4 2 2 11/02/2001
4 2 2 15/12/1998
5 2 1           
5 2 1 07/07/2020
5 2 1 06/01/2020
;

proc sort data = have;
   by pid date_of_diagnosis;
run;

data want;
   set have;
   by pid date_of_diagnosis;
   if first.pid then order_of_tum = 0;
   if first.date_of_diagnosis then order_of_tum + 1;
run;

Re: comparing rows in a dataset

Anita_n — Wed, 17 Feb 2021 10:58:35 GMT

Thank you

Re: comparing rows in a dataset

Anita_n — Wed, 17 Feb 2021 11:53:49 GMT

Hi what happens when I have the same dates within a group? In that case don't want it to count further but out the number as previous.

for example if patient 1, there is a 4th row with

1 2 2 11/03/2019

I don't want to have order_of_tum like (1, 2, 3, 4) but (1, 2 ,3 ,3) any solution to that?