Select a highly specified group of individuals for a table

[joebacon]

For those of you who have been helping/following my SAS journey, I wanted to say I appreciate you for being patient with me.

With that being said, I am trying to wrap my head around how to select individuals in a longitudinal data set that have a first BMI at or after the age of 18 that is between 18.5 and 30, and then increase at least 5 BMI point AND have a BMI greater than 25 after the increase. 

The dataset looks like: 

data WORK.BMI_DOB;
  infile datalines dsd truncover;
  input ID:32. Gender:32. DOB_M:32. DOB_Y:32. Race:32. Year:32. Height:32. Weight:32. BMI:6.3 DOB:MONYY7. Age:32.;
  format BMI 6.3 DOB MONYY7.;

label ID="PUBID - YTH ID CODE 1997" Gender="1= Male, 2=Female" DOB_M="Date of Birth Month" DOB_Y="Date of Birth Year" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Survey took place" Height="Height in
 Inches" Weight="Weight in pounds" BMI="Body Mass Index";
datalines;
1 2 9 1981 4 1997 67 145 22.708 SEP1981 16
1 2 9 1981 4 1998 67 150 23.491 SEP1981 17
1 2 9 1981 4 1999 67 150 23.491 SEP1981 18
1 2 9 1981 4 2000 68 160 24.325 SEP1981 19
1 2 9 1981 4 2001 67 163 25.527 SEP1981 20
1 2 9 1981 4 2002 67 155 24.274 SEP1981 21
1 2 9 1981 4 2003 67 153 23.961 SEP1981 22
1 2 9 1981 4 2004 67 160 25.057 SEP1981 23
1 2 9 1981 4 2005 67 160 25.057 SEP1981 24
1 2 9 1981 4 2006 67 157 24.587 SEP1981 25
1 2 9 1981 4 2007 67 156 24.430 SEP1981 26
1 2 9 1981 4 2008 67 160 25.057 SEP1981 27
1 2 9 1981 4 2009 67 158 24.744 SEP1981 28
1 2 9 1981 4 2010 67 152 23.804 SEP1981 29
1 2 9 1981 4 2011 67 155 24.274 SEP1981 30
1 2 9 1981 4 2013 67 152 23.804 SEP1981 32
1 2 9 1981 4 2015 67 175 27.406 SEP1981 34
2 1 7 1982 2 1997 67 135 21.142 JUL1982 15
2 1 7 1982 2 1998 69 150 22.149 JUL1982 16
2 1 7 1982 2 1999 67 140 21.925 JUL1982 17
2 1 7 1982 2 2000 66 150 24.208 JUL1982 18
2 1 7 1982 2 2001 67 158 24.744 JUL1982 19
2 1 7 1982 2 2002 67 163 25.527 JUL1982 20
2 1 7 1982 2 2003 67 170 26.623 JUL1982 21
2 1 7 1982 2 2004 67 180 28.189 JUL1982 22
2 1 7 1982 2 2005 65 175 29.118 JUL1982 23
2 1 7 1982 2 2006 . . . JUL1982 24
2 1 7 1982 2 2007 . . . JUL1982 25
2 1 7 1982 2 2008 67 175 27.406 JUL1982 26
2 1 7 1982 2 2009 67 181 28.346 JUL1982 27
2 1 7 1982 2 2010 67 189 29.598 JUL1982 28
2 1 7 1982 2 2011 67 175 27.406 JUL1982 29
2 1 7 1982 2 2013 67 180 28.189 JUL1982 31
2 1 7 1982 2 2015 67 225 35.236 JUL1982 33
;;;;

The idea that I have right now is do it piecemeal such that :

proc sql;
create table TargetWeight as 
Select * from BMI_DOB
Where ID in (Select ID from BMI_DOB where Age ge 18 and BMI between 18.5 and 30);
quit;

This gets the first part that I need.

However, I can't wrap my head around how to do the second set of restrictions. I was trying iterations where I subtract each BMI for each ID by its baseline BMI between 18.5 and 30, but the baseline changes for each ID. For instance, one starts at age 18 where they have a BMI of 20.783 and another's first BMI between 18.5 and 30 is  at age 22 where their BMI is 18.672. I suppose I don't understand SAS well enough to call upon the right commands for the subset of a dataset that I want.

I always feel bad for asking what are seemingly easy questions, but I have learned a lot on this forum. For that, I am very grateful. If you ever have any tips or resources, I will gladly utilize these to their fullest.

[Reeza]

If you're up for it, the DoW loop can help resolve all of this, but is a bit complex to understand.

https://support.sas.com/resources/papers/proceedings14/1619-2014.pdf

I'm lazy, so I'd probably do each condition separately and then merge the three data sets. For baseline to end, you should really really spend some time understanding BY group processing. It's one of the most powerful features of SAS.

https://documentation.sas.com/?docsetId=lrcon&docsetTarget=n01a08zkzy5igbn173zjz82zsi1s.htm&docsetVe...

[joebacon]

Shall check out and read up on both, thank you!

If YOU are saying something is important, than I most certainly will do my best to understand it to the fullest.

Like always, thanks! You're the best.

[s_lassen · Accepted solution]

This should give you all the rows with the relevant differences:

proc sql;
create table TargetWeight as
Select BMI1.*,BMI2.year as year2,bmi2.BMI as BMI2 from BMI_DOB BMI1, BMI_DOB BMI2
Where BMI1.Age ge 18
   and BMI1.BMI between 18.5 and 30
   and BMI2.ID=BMI1.ID
   and BMI2.year>BMI1.year
   and BMI2.BMI>=BMI1.BMI+5
   and BMI2.BMI>25
   ;
quit;

[joebacon]

This worked really well when combined with another SQL command to pull ID's from the new table, thank you!

Proc sql;
create table ActualTargetWeight as
select * from BMI_DOB where id in (select id from TargetWeight);
quit;

How you did it was really clever, too. Make two tables and merge them.

[joebacon]

I am not sure if you will see this, but i have a follow-up question. 

Is there a way to grab only those whose first adult (ages 18+) BMI between 18.5 and 30? 

For instance, there are cases where the initial BMI at age 18+ is well over 30. Then it drops to be within the 18.5 to 30 range where it then raises back 5 points over 30.

Therefore, it fits the criteria of the sas commands we used, but does not start between 18.5 and 30.

[FreelanceReinh]

---

@joebacon wrote:

Is there a way to grab only those whose first adult (ages 18+) BMI between 18.5 and 30? 

For instance, there are cases where the initial BMI at age 18+ is well over 30. Then it drops to be within the 18.5 to 30 range where it then raises back 5 points over 30.

---

Hi @joebacon,

If I may step in here: Yes, you could add such a restriction simply as another "and ..." condition in the WHERE clause. The question is: What is the exact criterion that you really want?

Example 1: A subject whose sequence of BMI values during adulthood was 29, 31, 27, 33 would meet the criterion "first adult BMI between 18.5 and 30," but at the same time it's a case where the BMI first drops into the range [18.5, 30] before the significant increase (here: from 27 to 33) occurs.

Example 2: Another subject's adult BMI sequence may start with 18, 21, 25, 31. The new criterion would exclude this subject (because 18<18.5) although it does show a significant increase (from 25 to 31).

Would it be more appropriate to require that no earlier adult BMI value of the subject was greater than the value from which the significant increase started? (This would exclude cases like "29, 27, 33," though.) This could be coded as

and not exists (select * from bmi_dob where id=bmi1.id & .<year<bmi1.year & age ge 18 & bmi>bmi1.bmi)