topic Re: How to increment an age between two dates in SAS Programming

How to increment an age between two dates

EricaW — Thu, 27 Aug 2015 19:39:54 GMT

Hi all I am bumping into a coding issue that has me stumped. I have 5 years of data for a children under 5 and I need to find for each year the number that turned specific ages who breastfed for specific time periods in a year. For example, I need to calculate all of the children who turned 1+ weeks old in the year who breastfed for more than one week, children who turned 2+ weeks old in the year who breastfed for more than two weeks, etc.

I have child DOB, date last asked about breastfeeding and whether they ever breastfed. I have sorted out how ages at the beginning and end of the time period and age at time of breastfeeding question but I keep ending up with too many kids in each age group, probably because I am pulling in everyone who was 1+weeks, 2+weeks, etc. old, rather than just those who turned that age during the time period. I am stumped on how to get only the kids that turned that age in the year, especially since kids pass from one age group to the next through out the year. So your 1 week olds become 2 weeks, become 3 weeks....up to 18 months.

Any help would be greatly appreciated! A portion of my code is below

Thanks,

Erica

data BFDuration;

set perm.dataset;

IF AGEMOS<60 AND
AGEMOS NE .;

%LET YR=YEAR;

Startperiod = MDY(1,1,&yr);

Endperiod = MDY(12,31,&yr);

*****Child birthday******;

child_dobmo=
substr(child_dob,1,2);

child_dobdy=
substr(child_dob,3,2);

child_dobyr=
substr(child_dob,5,4);

ChildDOB = MDY(child_dobmo,
child_dobdy, child_dobyr);

*********Last breastfeeding response*****

lstbrstfeddt_mo=
substr(lstbrstfed_dt,1,2);

lstbrstfeddt_dy=
substr(lstbrstfed_dt,3,2);

lstbrstfeddt_yr=
substr(lstbrstfed_dt,5,4);

DOBreastfed =
MDY(lstbrstfeddt_mo ,lstbrstfeddt_dy ,lstbrstfeddt_yr);

*****Age at last breastfeeding response in weeks and months****

BFAGEwk=INTCK('WEEK',CHILDDOB,DOBREASTFED);

BFAGEmo=INTCK('MONTH',CHILDDOB,DOBREASTFED);

****Currently or ever breastfed*****

if cur_brstfed='1' or
ever_brstfed='1' then brstfed='1';

if cur_brstfed='2' and
ever_brstfed='2' then brstfed='2';

if cur_brstfed~='' and
ever_brstfed='' then brstfed=cur_brstfed;

if cur_brstfed='' and
ever_brstfed~='' then brstfed=ever_brstfed;

*****Age at beginning and end of time period*****

if childdob<= MDY(1,1,&yr)
then DO;

AGESTARTwk=intck('WEEK',childdob,Startperiod);

AGESTARTmo=intck('MONTH',childdob,Startperiod);

AGEENDwk=intck('WEEK',childdob,Endperiod);

AGEENDmo=intck('MONTH',childdob,Endperiod)

END;

if childdob> MDY(1,1,&yr)then DO;

AGEENDwk=intck('WEEK',childdob,Endperiod);

AGEENDmo=intck('MONTH',childdob,Endperiod);

END;

IF AGESTARTwk<=1
AND AGESTARTwk NE . AND brstfed=1 THEN DO;

IF BFAGEwk>=1 THEN BF1WK=1;

IF BFAGEwk<=1 THEN BF1WK=0;

END;

IF AGEENDwk>1 AND

AGEENDWK NE . AND brstfed=1 THEN DO;

WEEK1=INTCK('WEEK',CHILDDOB,DOBreastfed);

IF BFAGEwk>=1 THEN BF1WK=1;

IF BFAGEwk<=1 THEN BF1WK=0;

END;

IF brstfed=2 THEN DO;

BF1WK=0;

END;

Re: How to increment an age between two dates

ballardw — Thu, 01 Oct 2015 17:25:23 GMT

### NOTE TO CASUAL READERS: The code below was developed as part of my work and vastly exceeds the amount of effort I would put into a routine response for help on this forum. I just happen to work with similar data and recognized the project. If you are interested you might search the CDC.GOV website for PedNSS and PNSS for details of some of the scope involved.

I suspect you are trying to duplicate the code behavior from PedNSS (if that looks familiar).

I'm going to paste some of my code that uses some similar variables. I think the names are sufficiently close you can modify the code below.

this label block describes the variables, may look somewhat familiar

label

dobrst = "Date of breastfeeding information"

x_bfcnt = "Ever BF -numerator"

x_bftot = "Ever BF -denominator"

x_bf1wcnt = "BF 1+ Weeks -numerator"

x_bf1wktot = "BF 1+ Weeks -denominator"

x_bf2wcnt = "BF 2+ Weeks -numerator"

x_bf2wktot = "BF 2+ Weeks -denominator"

x_bf3wcnt = "BF 3+ Weeks -numerator"

x_bf3wktot = "BF 3+ Weeks -denominator"

x_bf4wcnt = "BF 4+ Weeks -numerator"

x_bf4wktot = "BF 4+ Weeks -denominator"

x_bf6wcnt = "BF 6+ Weeks -numerator"

x_bf6wktot = "BF 6+ Weeks -denominator"

x_bf2mcnt = "BF 2+ Months -numerator"

x_bf2mtot = "BF 2+ Months -denominator"

x_bf3mcnt = "BF 3+ Months -numerator"

x_bf3mtot = "BF 3+ Months -denominator"

x_bf4mcnt = "BF 4+ Months -numerator"

x_bf4mtot = "BF 4+ Months -denominator"

x_bf5mcnt = "BF 5+ Months -numerator"

x_bf5mtot = "BF 5+ Months -denominator"

x_bf6mcnt = "BF 6+ Months -numerator"

x_bf6mtot = "BF 6+ Months -denominator"

x_bf9mcnt = "BF 9+ Months -numerator"

x_bf9mtot = "BF 9+ Months -denominator"

x_bf12mcnt = "BF 12+ Months -numerator"

x_bf12mtot = "BF 12+ Months -denominator"

x_bf18mcnt = "BF 18+ Months -numerator"

x_bf18mtot = "BF 18+ Months -denominator"

x_bfe3mcnt = "Exclusively BF 3+ Months -numerator"

x_bfe3mtot = "Exclusively BF 3+ Months -denominator"

x_bfe6mcnt = "Exclusively BF 6+ Months -numerator"

x_bfe6mtot = "Exclusively BF 6+ Months -denominator"

dov ="Date of Visit, SAS date value"

dob ="Date of Birth, SAS date value"

;

/*I create a variable that combines the current and ever breast fed variables so I simplify some comparisons*/

/* there may be some "error" adjustments that were peculiar to my data source, if you don't have the issues

I did the code should be dormant*/

   length tstrng $ 2;
   tstrng = cats(cbrstfd,evrbrstfd);
   if tstrng = '11' then do;
      if 0 le lgthbrst le 96 then cbrstfd='2';
      else evrbrstfd='9';
   end;
   /* errors in supplemental feed, often larger than period breast feed
   Also value of 77 should ONLY be when cbrstfd=1
   */
/*   if tstrng in ( '22') and lgthbrst ne 99 then do;*/
/*      cbrstfd='9'; */
/*      evrbrstfd='1';*/
/*      lgthbrst=99;*/
/*      tstrng = cats(cbrstfd,evrbrstfd);*/
/*   end;*/
   if tstrng in ( '22') then do;
      lgthbrst = 99 ;
      suppfeed = 99;
   end;

tstrng = cats(cbrstfd,evrbrstfd);*reset the tststrng after correction;

x_bftot = (year(dob)=year(dov) and tstrng in('19','91','21','22'));

if x_bftot = 1 then x_bfcnt = ( cbrstfd='1' or evrbrstfd='1');

array day [15] _temporary_ (7 14 21 28 42 61 92 122 153 183 274 366 548 92 183);

array bftot x_bf1wktot x_bf2wktot x_bf3wktot x_bf4wktot x_bf6wktot

x_bf2mtot x_bf3mtot x_bf4mtot x_bf5mtot x_bf6mtot

x_bf9mtot x_bf12mtot x_bf18mtot x_bfe3mtot x_bfe6mtot

;

array bfcnt x_bf1wcnt x_bf2wcnt x_bf3wcnt x_bf4wcnt x_bf6wcnt

x_bf2mcnt x_bf3mcnt x_bf4mcnt x_bf5mcnt x_bf6mcnt

x_bf9mcnt x_bf12mcnt x_bf18mcnt x_bfe3mcnt x_bfe6mcnt

;

days = dov-dob; *a temporary variable used in some comparisons;

do _i_= 1 to 15;

/* start denominator assignment, default to NO/ 0 */

bftot[_i_]=0 ;

if year(DOB + day[_i_]) = year(dov) then do;

/* tstdate is a temporary variable that contains value based on the parameter

in the DAY array added to DOB to test if the result is within the time frame

of valid data for the year (date of visit)

tstdate = DOB + day[_i_];

format tstdate mmddyy10.;

/* if birth date plus increment is later than date of visit then not valid to consider*/

if tstdate > DOV then bftot[_i_]=0;

/* if age at visit more than 24 months (732 days)

then over date range of interest*/

else if dov > (dob+732) then bftot[_i_]=0;

/* set valid denominator only when a valid breastfeeding info may be present*/

else if tstrng in ('99','91') then bftot[_i_]=0;

else bftot[_i_] = 1;

/* the exclusive BF have an different tests to pass*/

if _i_ in (14,15) then do;

bftot[_i_]=0;

if tstrng in('21','19') and days ge day[_i_] then bftot[_i_]=1;

end;

else bftot[_i_] = 0;

/* end denominator assignment and start numerator*/

/* ONLY ASSIGN NUMERATOR WHERE THE DENOMINATOR IS 1!!! Likely we have

another hidden issue about weeks here and original data collection

ltdays = (lgthbrst < 99)*lgthbrst*7;

IF bftot[_i_]=1 then do;

bfcnt[_i_]=0;

/* If 1) currently breast feeding and time is long enough then counts

OR if ever bf but stopped and weeks BF info is long enough then assign

wk2days = (lgthbrst < 99)*lgthbrst*7;

/* here's the special week to days adjustment*/

select ( lgthbrst );

when (13) wk2days= 92;

when (17) wk2days=122;

when (21) wk2days=153;

when (26) wk2days=183;

when (39) wk2days=274;

when (52) wk2days=366;

when (78) wk2days=548;

otherwise; *do nothing the value previous calculated should be sufficient;

end;

if cbrstfd='1' or (evrbrstfd='1' and (wk2days ge day[_i_]))

then bfcnt[_i_]=1;

if _i_ in (14,15) then do;

/* if supplement foods were introduced before the minimum date of the exclusive feeding period

then NOT exclusive. Note on Intro Supp Feeding coding: 31 = 31 or more weeks

77 = currently/only breastfed, 99 missing or not applicable (supposedly also

never breast fed BUT also seems to be automatically assigned if age is 24 months

or older. Since 31 weeks is greater than 6 months, which is the max value we

worry about then the calc should be okay

supdays= (suppfeed< 99)*(suppfeed*7);

/* similar adjustment for days before suplementary feeding*/

if suppfeed=13 then supdays= 92;

else if suppfeed=26 then supdays=183;

if bfcnt[_i_]=1 and (supdays lt day[_i_]) then bfcnt[_i_]=0;

end; *exculsive numerator;

end;*numerator assignment;

end;

/* I'm investigating using the number of defined numerators as the selection criteria for keeping records*/

NumeratorCount = N(of bfcnt

);

/* remove the temporary variables, uncomment for debugging*/

/* drop days tstrng tstdate ltdays wk2days supdays*/

/* ;*/

Re: How to increment an age between two dates

EricaW — Thu, 27 Aug 2015 23:45:33 GMT

Hi BallardW,

You got it in one! It is PedNSS and, looking at your code, I probably would not have figured this out on my own....or at least not any time soon, so thank you. Just for my own understanding, what you are doing is adding days in your array to confirm that someone is in the denominator and, if they are, classifying them as breastfed or not breastfed? And once they stop being breastfed they drop out of the numerator, into the denominator and continue along until the age out (in my case at age 5)?

Thank you again!

Erica

Re: How to increment an age between two dates

ballardw — Fri, 28 Aug 2015 14:39:14 GMT

Yes, that's what I'm doing. And it did take more than a couple hours to work this out. I spent some time going through the code repository that CDC had developed for PedNSS processing and then tested my submission files against copies of CDC supplied "master" single record files. I think I had a few differences from my raw submissions but they were due to record exclusions.

In our case there isn't any actual breastfeeding information is records for children seen at some age around 24 months. So the extracts had all 9, not reported data, at that point.

This code does calculate for each visit so a further filter may be needed to "final" status for a period. With 1/0 coding that's easy with Proc Summary looking for the MAX value of the numerator and denominator values.

If I need to know the BF history of a child at age 5 I merge in data from the time with the

Re: How to increment an age between two dates

EricaW — Fri, 28 Aug 2015 15:39:52 GMT

What I am running into is that there is no "Date of Visit" variable in this extract, or at least no date that is intuitive. Unfortunately our WIC program does not have a good handle on the data. All they know is that what they gave me is what they gave to CDC and they want me to make the same table. We do have initial visit date (which may actually be date of visit) and I suspect that when the quarterly files were merged only one visit was kept which is making this analysis more painful than necessary.

Do you think appending the quarters for the 5 years (under the assumption that the kids have at least one record for each year) and then transposing "Initial visit" into Initial Visit1--Initial VisitN , then using the last visit that is within the year of reporting as a DOV in your code might work? As my extract stands, that is the only date that is not tied to a specific biometric measure (e.g. breastfeeding, height, weight)

Re: How to increment an age between two dates

ballardw — Fri, 28 Aug 2015 17:00:36 GMT

If the file was created in the same format as a PedNSS submission file then the date should be in columns 20-27 and read it with an MMDDYY8. format. Also if this is PedNSS data there should be a column for date of most recent breastfeeding response which should substitute for DOV if different than the DOV (not often in my data).

Initial Visit Date sounds like a PNSS data extract with mothers information which would not have sufficient information to create the time interval breast feeding variables such as introduction of supplementary feeding and length of time breastfed and should only be one record per report year.

Re: How to increment an age between two dates

EricaW — Fri, 28 Aug 2015 17:42:25 GMT

OK, it looks like what my import statement (some legacy code from an epi from long, long ago) calls intvistdt is what you call DOV since it is positions 20-27 (see below). Why on earth everything was done as characters I have no clue, but I am going to fix it when I am past the "DO THIS NOW!" crunch,

Thank you so much for your help because I was seriously flying blind!

data pednss_2_2012;

infile 'G:\.....\XAZ_PedNSS_2012_Q02_20120925.dat' LRECL=400;

input substate $3. clinic $9. zipcode $5. source_data $1. record_type $1. intvistdt $8. infantid $60. child_dob $8. gender $1. race $6. wic_race $3. contr_race $2.

hholdsize $2. hholdmoincome $4. migrant $1. wic $1. foodstp $1. medicaid $1. tanf $1. bwtenglish $4. bwtmetric $4. htinch $3. htmetric $4. wtenglish $4. wtmetric $4.

htwtmeas_dt $8. hgbin $3. hmct $3. hgbhmctmeas_dt $8. cur_brstfed $1. ever_brstfed $1. brstfed_length $2. lstbrstfed_dt $8. intro_suppl $2. tv $1. hshld_smokn $1.;

run;

Re: How to increment an age between two dates

ballardw — Fri, 28 Aug 2015 19:46:25 GMT

Generally if it isn't a value to do arithmetic with I leave them as character. Exceptions are sometimes Yes/no codes that I'll make 1/0 with custom informats.

The date variables need to be SAS dates valued or you'll get nowhere with the other code.

Here's part of my code to read a PedNSS submission file

Length

state $ 2 substate $ 1 clinic $ 9 zip $ 5 sod 3 rcdtyp $ 1

dov 8 idchild $ 30 dob 8 sex 3 race $ 6 wicrace $ 3 contrace $ 2

hhsize 3 income 8 migrant $ 1 wic $ 1 stamps $ 1 medicaid $ 1

tanf $ 1 bwteng 8 bwtmtr 8 hgteng 3 hgtmtr 8 wgteng 8 wgtmtr 8

doght 8 hemogl 3 hemato 3 dohemo 8 cbrstfd $ 1 evrbrstfd $ 1

lgthbrst 3 dobrst 8 suppfeed 3 tvview $ 1 hhsmoke $ 1

;

   INPUT
   state 1-2 substate 3 clinic 4-12 zip 13-17 sod 18
   rcdtyp 19 dov mmddyy8. idchild 28-57 @88 dob mmddyy8.   sex 96
   race 97-102 wicrace 103-105 contrace 106-107 hhsize 108-109
   income 110-113 migrant 114 wic 115 stamps 116 medicaid 117
   tanf 118 bwteng 119-122 bwtmtr 123-126 hgteng 127-129
   hgtmtr 130-133 wgteng 134-137 wgtmtr 138-141 doght ?? mmddyy8.
   hemogl 150-152 hemato 153-155 dohemo ?? mmddyy8. cbrstfd 164
   evrbrstfd 165 lgthbrst 166-167 dobrst ?? mmddyy8. suppfeed 176-177
   tvview 178 hhsmoke 179 ;
   /* currently the documented data stops at column 179. Columns 180-209 */
   /* are reserved for future CDC use. Columns 210+ are for state added */
   /* data. We do have some thing showing up in columns 210 and 211 but */
   /* it is not documented.                                              */

   LABEL
   state     = "State"
   substate = "Substate"
   clinic    = "Clinic/School"
   zip       = "Zip Code"
   sod       = "Source of Data"
   rcdtyp    = "Record Type"
   dov       = "Date of Visit"
   idchild   = "Child’s Alphanumeric ID"
   dob       = "Date of Birth"
   sex       = "Sex"
   race      = "Race/Ethnicity (6 level)"
   hisp      = "Ethnicity (1=Hispanic,0=Non-hispanic)"
   wicrace   = "WIC Race/Ethnicity"
   contrace = "Contributor-Specific Race/Ethnicity"
   hhsize    = "Household Size"
   income    = "Monthly Household Income"
   migrant   = "Migrant Status"
   wic       = "WIC"
   stamps    = "Food Stamps"
   medicaid = "Medicaid"
   tanf      = "TANF"
   bwteng    = "Birthweight English (LBOz)"
   bwtmtr    = "Birthweight Metric (gm)"
   hgteng    = "Height English (InIn 1/8ths)"
   hgtmtr    = "Height Metric (mm)"
   wgteng    = "Weight English (LB fourths)"
   wgtmtr    = "Weight Metric (kg implied decimal)"
   doght     = "Date of Height/Weight Measure"
   hemogl    = "Hemoglobin (g/Dl impled decimal)"
   hemato    = "Hematocrit (% implied tenth)"
   dohemo    = "Date of Hemoglobin/Hematocrit Measure"
   cbrstfd   = "Currently Breastfed"
   evrbrstfd = "Ever Breastfed"
   lgthbrst = "Length of Time Breastfed (if ever and stopped) weeks 99=none/unk"
   dobrst    = "Date of Most Recent Breastfeeding"
   suppfeed = "Introduction to Supplementary Feeding, age in weeks"
   tvview    = "TV Viewing"
   hhsmoke   = "Household Smoking"
   distr     = "WIC District"
   ;
   format dov dob doght dohemo dobrst mmddyy10.;

Re: How to increment an age between two dates

EricaW — Fri, 28 Aug 2015 20:04:43 GMT

Thank you again! I have been substringing the characters into separate mm, dd, and yy variables and then turning them into SAS dates like this:

lstbrstfeddt_mo= substr(lstbrstfed_dt,1,2);

lstbrstfeddt_dy= substr(lstbrstfed_dt,3,2);

lstbrstfeddt_yr= substr(lstbrstfed_dt,5,4);

dobrstfd = MDY(lstbrstfeddt_mo ,lstbrstfeddt_dy ,lstbrstfeddt_yr);

which is a pretty silly, high effort way to do it, now that I think about it. I was being lazy about changing the import code and created more work for myself, I do believe. It made sense at the time, I suppose, but that can be said of most bad decisions

Re: How to increment an age between two dates

ballardw — Fri, 28 Aug 2015 21:26:00 GMT

If that's the worst extra work you ever cause yourself then be happy.

When I inherited some of this I reduced some of the code from 8 or 9 datasets and about 800 lines of code to a single data step with roughly 200 lines, and many of those lines were labels to document what the variables were supposed to be. And code exactly like you posted was part of it.

Learn Proc Format, invalue and cntlin and cntlout options and there are quite a few tasks that get much easier for reading data.

Re: How to increment an age between two dates

EricaW — Wed, 09 Sep 2015 19:15:49 GMT

Hi Ballard,

Thank you again for all of your help. I was able to use your code to get withing 0.X% of CDC's numbers and get the totals within +/- 5-10 people. The tweak to my data was that current breasteeding and ever breastfed did not have missing coded as 9, they were just blank, so I recoded the missing. Worked like a charm!

Thanks for everything,

Erica