How to indicate duplicates in long format data set

ginak — Tue, 11 Mar 2014 23:32:46 GMT

Hi! I have a question regarding how to indicate a value is repeated in a data that's long format.

Example:

ID name_of_former_variable ageofonset numberoftraumas

1 ageonsettrauma14 12 1

1 ageonsettrauma7 13 2

2 ageonsettrauma6 8 1

2 ageonsettrauma7 8 2

2 ageonsettrauma16 8 3

2 ageonsettrauma18 8 4

2 ageonsettrauma20 9 5

3 ageonsettrauma4 5 1

3 ageonsettrauma18 5 2

So basically, numberoftraumas is a counter variable to see the number of traumas each ID endorsed.

I am interested in earliest age of onset (so for ID # 1, it is 12, for ID #2 it is 8). I also want to know

the number of traumas endorsed (So for ID #1 it is 2, for ID #2 it is 5). I can get these by splitting these

into two data sets and merging the largest "numberoftraumas" variable with the smallest "ageofonset"

THen I was able to use a substring function to indicate which trauma # is associated with the earliest

age of onset. But I'd like to indicate if there is a tie. So for example, for ID #2, the earliest age of

onset is 8. However, there are multiple traumas endorsed at age 8. I want to create a variable which

indicates if there are ties, specifically, the first two traumas (because I want to indicate that they had

multiple traumas at that earliest age of onset, if that makes sense).

I can't just use the largest "numberoftraumas' to indicate this because, as you can see with ID #1,

even though they had two traumas, they occurred at two different ages.

So if subject has multiple traumas occurring at the earliest age of trauma, then their 'firsttrauma'=99

Otherwise, it is the number of the trauma.

So for ID1, their earliest age of trauma was trauma#14 and occured at age 12.

No other trauma was endorsed at age 12, so their firsttrauma variable is 14.

But for ID2, they had 4 traumas at age 8, and one trauma at age 9, so

I don't care which traumas they experienced at age 8, I just want to indicate

that there was more than one experienced at age 8, so their firsttrauma=99.

ID3 had 2 traumas at age 5, so their firsttrauma=99.

So this is what I will see:

ID name_of_former_variable ageofonset numberoftraumas firsttrauma

1 ageonsettrauma14 12 1 14

2 ageonsettrauma20 8 5 99

3 ageonsettrauma18 5 2 99

But ultimately, I don't care about the name_of_former_variable so I'd delete that and want to see this:

ID ageofonset numberoftraumas firsttrauma

1 12 1 14

2 8 5 99

3 5 2 99

Thank you so much!! Sorry for the long explanation. I wasn't sure how to find this online , I couldn't find anything in my searches

Last time I asked a question someone requested for more details so I thought I'd provide more.

Re: How to indicate duplicates in long format data set

Reeza — Wed, 12 Mar 2014 00:59:23 GMT

I'd guess there was an easier way to do this with the original data or by merging it in, but I'll provide a data step solution based on your data.

My conversion to number may be off, I always get put/input wrong.

Untested.

data want;

set have;

by id;

retain first_age firsttrauma;

if first.id then do;

firsttrauma=input(compress(name, 'kd'), 2.);

first_age=ageofonset;

end;

if not first.id and ageofonset=first_age then firsttrauma=99;

if last.id then output;

end;

Re: How to indicate duplicates in long format data set

ginak — Wed, 12 Mar 2014 02:51:37 GMT

Hi Reeza!

Thank you so much. The code worked I just had to change input to put. You always are so fast to respond and help me, which I really appreciate. I am, unfortunately, not the best programmer, but try to google things before I come here and ask you guys. If you have any advice on how I can learn little tricks like the one you showed me, I'd really appreciate it! Just simple things like using the retain, first.variable types of statements.

Thanks again!!!!

topic Re: How to indicate duplicates in long format data set in SAS Procedures

How to indicate duplicates in long format data set

Re: How to indicate duplicates in long format data set

Re: How to indicate duplicates in long format data set