topic Re: Eliminating duplicates when sorting by unique test results in SAS Programming

Eliminating duplicates when sorting by unique test results

Lefty — Fri, 31 Aug 2018 23:16:03 GMT

Hi, I am using SAS 9.4 and am having trouble getting the data organized how I would like.

Here is an example of data I have:

ID Testdate TestResultPositive

1 -10 1

1 0 1

1 19 1

2 -30 1

2 0 0

2 15 1

2 45 1

I don't care about how many of the testdays had the same test results, but I am interested in whether test results went from positive to negative and back to positive again over time. So here is how I would like my data to look:

ID Day TestResultPositive

1 -10 1

2 -30 1

2 0 0

2 15 1

When i sort by id and testresultpositve with the nodupkey option, I wind up getting this (which loses the information about ID 2 having another positive test result on day 15):

proc sort data=have nodupkey; by id testresultpositive; run;

ID Day TestResultPositive

1 -10 1

2 -30 1

2 0 0

Any other ideas? Thanks so much in advance!

Re: Eliminating duplicates when sorting by unique test results

novinosrin — Fri, 31 Aug 2018 23:56:35 GMT

what is your expected result for your input sample?

Should i assume you are looking to for something like this?


data have;
input ID              Testdate       TestResultPositive;
cards;
1                -10                   1
1                     0                   1
1                   19                   1
2                 -30                   1
2                   0                      0
2                   15                     1
2                    45                    1
;

data want;
set have;
by id TestResultPositive notsorted;
if first.TestResultPositive;
run;

Re: Eliminating duplicates when sorting by unique test results

uopsouthpaw — Fri, 31 Aug 2018 23:51:31 GMT

Have you attempted looping a lag function over the id?