Generate Missing Values if a marker variable is True

[joebacon]

Hi all I hope youre having a good Tuesday!

I am in need of some guidance in a way to generate missing values that are contingent upon another variable being True.

A sample of my data is here: 

label ID="PUBID - YTH ID CODE 1997" Gender="1= Male, 2=Female" DOB_M="Date of Birth Month" DOB_Y="Date of Birth Year" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
4 2 2 1981 . . 2 1997 61 186 35.141 . . FEB1981 16
4 2 2 1981 . . 2 1998 62 190 34.748 0 . FEB1981 17
4 2 2 1981 . . 2 1999 62 185 33.833 . 0 FEB1981 18
4 2 2 1981 . . 2 2000 62 185 33.833 . 0 FEB1981 19
4 2 2 1981 . . 2 2001 62 200 36.576 . 0 FEB1981 20
4 2 2 1981 . . 2 2002 62 236 43.160 0 1 FEB1981 21
4 2 2 1981 . . 2 2003 62 195 35.662 . 0 FEB1981 22
4 2 2 1981 . . 2 2004 62 250 45.721 . 0 FEB1981 23
4 2 2 1981 . . 2 2005 62 230 42.063 . 0 FEB1981 24
4 2 2 1981 . . 2 2006 62 279 51.024 . 0 FEB1981 25
4 2 2 1981 . . 2 2007 62 280 51.207 . 0 FEB1981 26
4 2 2 1981 . . 2 2008 61 140 26.450 . 0 FEB1981 27
4 2 2 1981 . . 2 2009 62 175 32.004 0 1 FEB1981 28
4 2 2 1981 . . 2 2010 61 160 30.228 . 0 FEB1981 29
4 2 2 1981 . . 2 2011 61 170 32.118 . 0 FEB1981 30
;;;;

***The data continues in this longitudinal pattern with a new observation. I just didn't print enough observations to show that.

What I would like to do is whenever a person got pregnant (Pregnant =1 or Pregnant_LastInt=1), their BMI values are negated from analyses for that year and the following years. I was thinking that the best way to do this is make them missing values.

I am thinking this could be done with a Do Loop or some sort of SQL processing. I tried my hand at a DO Loop that seemed to go on infinitely.

Any help in handling this would be awesome!

I have tried iterations of the following to no avail:

data preggo;
set preggo;
if pregnant =1 or pregnant_lastint = 1 then bmi = . ;
do until (year = 2015);
end;
run;
**This looks off because it appears SAS is looking for the 1's and not anything after**

Thank you in advance. 

[PeterClemmensen]

Can you please provide your entire data step creating the example data?

[joebacon]

My apologies, I didnt even realize I cut it off!

Here it is:

data WORK.PREGGO;
  infile datalines dsd truncover;
  input ID:32. Gender:32. DOB_M:32. DOB_Y:32. Sample Type:32. Race:32. Year:32. Height:32. Weight:32. BMI:6.3 Pregnant:32. Pregnant_LastInt:32. 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" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
4 2 2 1981 . . 2 1997 61 186 35.141 . . FEB1981 16
4 2 2 1981 . . 2 1998 62 190 34.748 0 . FEB1981 17
4 2 2 1981 . . 2 1999 62 185 33.833 . 0 FEB1981 18
4 2 2 1981 . . 2 2000 62 185 33.833 . 0 FEB1981 19
4 2 2 1981 . . 2 2001 62 200 36.576 . 0 FEB1981 20
4 2 2 1981 . . 2 2002 62 236 43.160 0 1 FEB1981 21
4 2 2 1981 . . 2 2003 62 195 35.662 . 0 FEB1981 22
4 2 2 1981 . . 2 2004 62 250 45.721 . 0 FEB1981 23
4 2 2 1981 . . 2 2005 62 230 42.063 . 0 FEB1981 24
4 2 2 1981 . . 2 2006 62 279 51.024 . 0 FEB1981 25
4 2 2 1981 . . 2 2007 62 280 51.207 . 0 FEB1981 26
4 2 2 1981 . . 2 2008 61 140 26.450 . 0 FEB1981 27
4 2 2 1981 . . 2 2009 62 175 32.004 0 1 FEB1981 28
4 2 2 1981 . . 2 2010 61 160 30.228 . 0 FEB1981 29
4 2 2 1981 . . 2 2011 61 170 32.118 . 0 FEB1981 30
4 2 2 1981 . . 2 2013 61 200 37.786 . 0 FEB1981 32
4 2 2 1981 . . 2 2015 61 190 35.896 . . FEB1981 34
11 2 6 1982 . . 2 1997 64 120 20.596 . . JUN1982 15
11 2 6 1982 . . 2 1998 65 130 21.631 . . JUN1982 16
11 2 6 1982 . . 2 1999 64 123 21.111 . 0 JUN1982 17
;;;;

[Reeza · Accepted solution]

1. Your data has no variables names and can't really be used as posted.

2. Set a flag if pregnancy = 1 and use RETAIN to hold it. Also set a YEAR_STOP to Year+1 so that you know to reset it after that year.

3. If Year > Year_Stop then reset the flag and stop setting to missing. 

data want;
set pregnant;

by id date;
retain preg_flag;

if pregnant=1 then do; 
    preg_flag = 1;
     year_stop = year+1; 
end;
if preg_flag = 1 and year>year_stop and pregnant=0 then call missing(preg_flag, year_stop);

run;

A SAS data step loops automatically, a loop in SAS is used to loop through variables on the same row, not from one row of data to the next.

---

@joebacon wrote:

Hi all I hope youre having a good Tuesday!

I am in need of some guidance in a way to generate missing values that are contingent upon another variable being True.

A sample of my data is here: 

label ID="PUBID - YTH ID CODE 1997" Gender="1= Male, 2=Female" DOB_M="Date of Birth Month" DOB_Y="Date of Birth Year" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
4 2 2 1981 . . 2 1997 61 186 35.141 . . FEB1981 16
4 2 2 1981 . . 2 1998 62 190 34.748 0 . FEB1981 17
4 2 2 1981 . . 2 1999 62 185 33.833 . 0 FEB1981 18
4 2 2 1981 . . 2 2000 62 185 33.833 . 0 FEB1981 19
4 2 2 1981 . . 2 2001 62 200 36.576 . 0 FEB1981 20
4 2 2 1981 . . 2 2002 62 236 43.160 0 1 FEB1981 21
4 2 2 1981 . . 2 2003 62 195 35.662 . 0 FEB1981 22
4 2 2 1981 . . 2 2004 62 250 45.721 . 0 FEB1981 23
4 2 2 1981 . . 2 2005 62 230 42.063 . 0 FEB1981 24
4 2 2 1981 . . 2 2006 62 279 51.024 . 0 FEB1981 25
4 2 2 1981 . . 2 2007 62 280 51.207 . 0 FEB1981 26
4 2 2 1981 . . 2 2008 61 140 26.450 . 0 FEB1981 27
4 2 2 1981 . . 2 2009 62 175 32.004 0 1 FEB1981 28
4 2 2 1981 . . 2 2010 61 160 30.228 . 0 FEB1981 29
4 2 2 1981 . . 2 2011 61 170 32.118 . 0 FEB1981 30
;;;;

***The data continues in this longitudinal pattern with a new observation. I just didn't print enough observations to show that.

What I would like to do is whenever a person got pregnant (Pregnant =1 or Pregnant_LastInt=1), their BMI values are negated from analyses for that year and the following years. I was thinking that the best way to do this is make them missing values.

I am thinking this could be done with a Do Loop or some sort of SQL processing. I tried my hand at a DO Loop that seemed to go on infinitely.

Any help in handling this would be awesome!

I have tried iterations of the following to no avail:

data preggo;
set preggo;
if pregnant =1 or pregnant_lastint = 1 then bmi = . ;
do until (year = 2015);
end;
run;
**This looks off because it appears SAS is looking for the 1's and not anything after**

Thank you in advance. 

---

[joebacon]

@Reeza 

I was finally attempting my iteration of this code which I modified a bit. However, the flag did not reset. So, when attempting to convert BMI into missing values, it turned everything after the first flag into missing values.

The data from before was the following: 

data WORK.ACTUALTARGETWEIGHT;
  infile datalines dsd truncover;
  input ID:32. Gender:32. DOB_M:32. DOB_Y:32. Sample Type:32. Race:32. Year:32. Height:32. Weight:32. BMI:6.3 Pregnant:32. Pregnant_LastInt:32. 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" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
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
4 2 2 1981 . . 2 1997 61 186 35.141 . . FEB1981 16
4 2 2 1981 . . 2 1998 62 190 34.748 0 . FEB1981 17
4 2 2 1981 . . 2 1999 62 185 33.833 . 0 FEB1981 18
;;;;

I used this iteration of the code you provided:

data PreggoFix;
set actualtargetweight;

by id age;
retain preg_flag;

if pregnant=1 or Pregnant_LastInt =1 then do; 
    preg_flag = 1;
     year_stop = year; 
end;
if preg_flag = 1 and year>year_stop and pregnant=0 and Pregnant_LastInt =0 then call missing(preg_flag, year_stop);

run;

data preggofix;
set preggofix;
if year>year_stop then bmi=.p;
drop year_stop preg_flag;
run;

Which resulted in first this:

data WORK.PREGGOFIX;
  infile datalines dsd truncover;
  input ID:32. Gender:32. DOB_M:32. DOB_Y:32. Sample Type:32. Race:32. Year:32. Height:32. Weight:32. BMI:6.3 Pregnant:32. Pregnant_LastInt:32. DOB:MONYY7. Age:32. preg_flag:32. year_stop: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" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
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 . .
4 2 2 1981 . . 2 1997 61 186 35.141 . . FEB1981 16 . .
4 2 2 1981 . . 2 1998 62 190 34.748 0 . FEB1981 17 . .
4 2 2 1981 . . 2 1999 62 185 33.833 . 0 FEB1981 18 . .
4 2 2 1981 . . 2 2000 62 185 33.833 . 0 FEB1981 19 . .
4 2 2 1981 . . 2 2001 62 200 36.576 . 0 FEB1981 20 . .
4 2 2 1981 . . 2 2002 62 236 43.160 0 1 FEB1981 21 1 2002
4 2 2 1981 . . 2 2003 62 195 35.662 . 0 FEB1981 22 1 .
4 2 2 1981 . . 2 2004 62 250 45.721 . 0 FEB1981 23 1 .
4 2 2 1981 . . 2 2005 62 230 42.063 . 0 FEB1981 24 1 .
4 2 2 1981 . . 2 2006 62 279 51.024 . 0 FEB1981 25 1 .
4 2 2 1981 . . 2 2007 62 280 51.207 . 0 FEB1981 26 1 .
4 2 2 1981 . . 2 2008 61 140 26.450 . 0 FEB1981 27 1 .
4 2 2 1981 . . 2 2009 62 175 32.004 0 1 FEB1981 28 1 2009
4 2 2 1981 . . 2 2010 61 160 30.228 . 0 FEB1981 29 1 .
4 2 2 1981 . . 2 2011 61 170 32.118 . 0 FEB1981 30 1 .
4 2 2 1981 . . 2 2013 61 200 37.786 . 0 FEB1981 32 1 .
4 2 2 1981 . . 2 2015 61 190 35.896 . . FEB1981 34 1 .
5 1 10 1982 . . 2 1997 65 135 22.463 . . OCT1982 15 1 .
5 1 10 1982 . . 2 1998 64 135 23.170 . . OCT1982 16 1 .
5 1 10 1982 . . 2 1999 66 145 23.401 . . OCT1982 17 1 .
5 1 10 1982 . . 2 2000 66 140 22.594 . . OCT1982 18 1 .
5 1 10 1982 . . 2 2001 66 170 27.436 . . OCT1982 19 1 .
5 1 10 1982 . . 2 2002 66 165 26.629 . . OCT1982 20 1 .
;;;;

And then after the second bit of code, this:

data WORK.PREGGOFIX;
  infile datalines dsd truncover;
  input ID:32. Gender:32. DOB_M:32. DOB_Y:32. Sample Type:32. Race:32. Year:32. Height:32. Weight:32. BMI:6.3 Pregnant:32. Pregnant_LastInt:32. 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" Sample Type="CV_SAMPLE_TYPE 1997" Race="1= Black, 2= Hispanic, 3= Mixed Race (Non-Hispanic) 4= Non-Black / Non-Hispanic" Year="Year the Su
rvey took place" Height="Height in Inches" Weight="Weight in pounds" BMI="Body Mass Index" Pregnant="Are you pregnant now?" Pregnant_LastInt="Have you been pregnant since the last interview?";
datalines;
2 1 7 1982 . . 2 1997 67 135 P . . JUL1982 15
2 1 7 1982 . . 2 1998 69 150 P . . JUL1982 16
2 1 7 1982 . . 2 1999 67 140 P . . JUL1982 17
2 1 7 1982 . . 2 2000 66 150 P . . JUL1982 18
2 1 7 1982 . . 2 2001 67 158 P . . JUL1982 19
2 1 7 1982 . . 2 2002 67 163 P . . JUL1982 20
2 1 7 1982 . . 2 2003 67 170 P . . JUL1982 21
2 1 7 1982 . . 2 2004 67 180 P . . JUL1982 22
2 1 7 1982 . . 2 2005 65 175 P . . JUL1982 23
2 1 7 1982 . . 2 2006 . . P . . JUL1982 24
2 1 7 1982 . . 2 2007 . . P . . JUL1982 25
2 1 7 1982 . . 2 2008 67 175 P . . JUL1982 26
2 1 7 1982 . . 2 2009 67 181 P . . JUL1982 27
2 1 7 1982 . . 2 2010 67 189 P . . JUL1982 28
2 1 7 1982 . . 2 2011 67 175 P . . JUL1982 29
2 1 7 1982 . . 2 2013 67 180 P . . JUL1982 31
2 1 7 1982 . . 2 2015 67 225 P . . JUL1982 33
4 2 2 1981 . . 2 1997 61 186 P . . FEB1981 16
4 2 2 1981 . . 2 1998 62 190 P 0 . FEB1981 17
4 2 2 1981 . . 2 1999 62 185 P . 0 FEB1981 18
4 2 2 1981 . . 2 2000 62 185 P . 0 FEB1981 19
4 2 2 1981 . . 2 2001 62 200 P . 0 FEB1981 20
4 2 2 1981 . . 2 2002 62 236 43.160 0 1 FEB1981 21
4 2 2 1981 . . 2 2003 62 195 P . 0 FEB1981 22
4 2 2 1981 . . 2 2004 62 250 P . 0 FEB1981 23
4 2 2 1981 . . 2 2005 62 230 P . 0 FEB1981 24
4 2 2 1981 . . 2 2006 62 279 P . 0 FEB1981 25
4 2 2 1981 . . 2 2007 62 280 P . 0 FEB1981 26
4 2 2 1981 . . 2 2008 61 140 P . 0 FEB1981 27
4 2 2 1981 . . 2 2009 62 175 32.004 0 1 FEB1981 28
4 2 2 1981 . . 2 2010 61 160 P . 0 FEB1981 29
4 2 2 1981 . . 2 2011 61 170 P . 0 FEB1981 30
4 2 2 1981 . . 2 2013 61 200 P . 0 FEB1981 32
4 2 2 1981 . . 2 2015 61 190 P . . FEB1981 34
5 1 10 1982 . . 2 1997 65 135 P . . OCT1982 15
5 1 10 1982 . . 2 1998 64 135 P . . OCT1982 16
5 1 10 1982 . . 2 1999 66 145 P . . OCT1982 17
5 1 10 1982 . . 2 2000 66 140 P . . OCT1982 18
5 1 10 1982 . . 2 2001 66 170 P . . OCT1982 19
5 1 10 1982 . . 2 2002 66 165 P . . OCT1982 20
;;;;

Any advice on why the flag didn't reset?

[Reeza]

Are you setting the persons BMI missing for the rest of their entries or just the year and year after?

Your initial example didn't include multiple ID's so the solution offered doesn't deal with that case. You added the BY statement but didn't reset anything by ID so nothing will reset.

[joebacon]

BMI will be missing for the year and each year after until..

I didn't know if SAS would recognize that the ID changed. It makes sense that I need to reset by ID. To reset by ID, could i just add another condition to the if clause at the end like "and ID=ID"?

[Reeza]

No, look up FIRST and LAST with BY group processing.

[joebacon]

After a bit of research on first and last by group processing, I am unsure why the following code processes for what I would assume would be infinitely.

data PreggoFix;
set actualtargetweight;

by id age pregnant Pregnant_LastInt ;
retain preg_flag;

if first.id and pregnant=1 or Pregnant_LastInt =1 then do until (last.id); 
    preg_flag = 1  ;
     year_stop = year; 
	
end;
if  preg_flag = 1 and year>year_stop and pregnant=0 and Pregnant_LastInt =0  then call missing(preg_flag, year_stop);

run;

I am trying to parse out where I need to add the first. or last. The following code made logical sense to me as well, but continues to give the string of preg_flag =1

data PreggoFix;
set actualtargetweight;

by id age pregnant Pregnant_LastInt ;
retain preg_flag;

if  pregnant=1 or Pregnant_LastInt =1 then do ; 
    preg_flag = 1  ;
     year_stop = year; 
	
end;
if last.id and preg_flag = 1 and year>year_stop and pregnant=0 and Pregnant_LastInt =0  then call missing(preg_flag, year_stop);

run;

[Reeza]

data PreggoFix;
set actualtargetweight;

by id age;
retain preg_flag;


if first.id then call missing(preg_flag);

if pregnant=1 or Pregnant_LastInt =1 then  preg_flag = 1;

if preg_flag = 1 then call missing(bmi);

run;

[joebacon]

First, thank you.

Its always way more simple than I think it needs to be.

I just needed to split it into 3 parts.

I won't waste anymore of your time, but if you have a good read for the "do until" bit, I would like to research how SAS processes that.

[Reeza]

DO UNTIL has two primary functions, one is a basic loop, and the second is for DoW loops. However, I've programmed for 15 years and I never use them. The DoW loops can definitely save you a lot of time and steps but it's a little more complex to understand. If you're interested in that functionality, this paper covers it.
https://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Otherwise, a basic DO UNTIL loop is rarely needed - not sure how it would have been used in this case.
DO UNTIL is documented here: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=lestmtsref&docsetTarget=p...

[Astounding]

Once you find a workable solution, consider using special values. For example

If .... then bmi=.p;

It's still a missing value and will be omitted from calculations. But looking at the data later you can tell which values were originally missing versus which ones you set to missing because of a pregnancy.

If you do go that route, this statement would not be sufficient to identify non missing values:

If BMI > . then do;

Instead you have to use:

If BMI > .p then do;

[joebacon]

Thank you! I didn't even know that was a SAS function but that would help with organization quite a bit.

I love learning new things.