BookmarkSubscribeRSS Feed
Lefty
Obsidian | Level 7

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!

 

2 REPLIES 2
novinosrin
Tourmaline | Level 20

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;
uopsouthpaw
Calcite | Level 5

Have you attempted looping a lag function over the id?

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1002 views
  • 0 likes
  • 3 in conversation