Change in weight every 30 days

[jmmedina25]

Hi all, I need to create a flagging code for a difference in person weight per month, where flag=1 if the participant lost >32 pounds over the course of month/30 days .  I'm not exactly sure how to do this b/c the dates are not exactly 30 days apart and some of the dates between participants aren't identical.  Data is sorted by ID and date. Here's what my data looks like:

HAVE DATA

ID             Date (MM/DD/YYYY)           Weight

1              02/01/2019                              240

1              02/08/2019                              237

1              03/04/2019                              202

1              04/06/2019                              236

2              02/01/2019                              170

2              02/08/2019                              167

2              03/03/2019                              165

2              04/07/2019                              105

WANT DATA

(if (abs.) difference in weight is >32 lbs from one month to the next month, flag=1)

ID             Date (MM/DD/YYYY)           Weight            Flag

1              02/01/2019                              240                  .

1              02/08/2019                              237                  0

1              03/04/2019                              202                  1

1              04/06/2019                              236                  1

2              02/01/2019                              170                  .

2              02/08/2019                              167                  0

2              03/03/2019                              165                  0

2              04/07/2019                              105                  1

[novinosrin]

Hi @jmmedina25   Please post a comprehensive  sample of 

1. Your HAVE data

2. Your WANT data aka expected OUTPUT for the sample INPUT

3. Explain your convert logic aka what you want to accomplish

Best Regards!

[jmmedina25]

Thanks for the tips! I hope my question is easier to understand now

[novinosrin · Accepted solution]

hi @jmmedina25  assuming i understand your question

data have;
input ID             Date :mmddyy10.        Weight;
format date mmddyy10.;
cards;
1              02/01/2019                              240
1              02/08/2019                              237
1              03/04/2019                              202
1              04/06/2019                              236
2              02/01/2019                              170
2              02/08/2019                              167
2              03/03/2019                              165
2              04/07/2019                              105
;

data want;
set have;
by id;
_k=lag(date);
if not first.id then _j=intck('month',_k,date);
_dif=dif(weight);
if missing(_dif) then _dif=0;
if abs(_dif)>32 and _j=1 then flag=1;
else flag=0;
if first.id then  flag=.;
drop _:;
run;

[VDD]

you need better dates.  How do we know if 2/1 means Feb-1 or Jan-2.  where is the year aspect in the dates.

if this is how someone supplied the dates to you send it back and tell them you need dates that are real dates that don't require the coder to make assumptions.  Editted

Thank you @jmmedina25 for giving us better dates to work with.

[PaigeMiller]

Why is FLAG=1 here?

1              04/06/2019                              236                  1

In your original description, you state you want to find places where a person loses >32 pounds in one month. This is not a 32 pound loss, it is a gain of 34 pounds. Later you say you want to find where abs(weight gain)>32 in one month. Which is it?

Also,

1              03/04/2019                              202                  1

is a 35 pound weight loss, but not over a 30 day period (I assume). So why is FLAG=1 here?

Lastly, the question from @VDD is not answered explicitly, so I am hoping you can provide a clear answer to this question: 

How do we know if 2/1 means Feb-1 or Jan-2.

[mkeintz]

Your problem is loosely defined, so there is probably extra code below.

This program:

1. Uses 2 temporary arrays of 30 elements each to hold the most recent 30 dates and 30 weights for a given id. "_temporary_" arrays have their values retained from observation to observations.  I use size 30, just in case you have daily weights.
2. clear the array every time you encounter a new id.  ("if first.id then call missing(.......);");
3. Have a counter (variable CTR) that goes from 1 to 30, (then if necessary back to 1) to track each incoming record and store its date and weight in the arrays.
4. Initialize the flag to missing for first record of an ID, or else to zero.
5. If you are at CTR>1, then compare the current weight to every preceding weight until you either go beyond 30 days back or to 32 pounds more (and flag is set to 1).

data have;
  input ID  Date :mmddyy10. Weight;
  format date date9.;
datalines;
1  02/01/2019  240
1  02/08/2019  237
1  03/04/2019  202
1  04/06/2019  236
2  02/01/2019  170
2  02/08/2019  167
2  03/03/2019  165
2  04/07/2019  105
run;

data want (drop=i ctr);
  set have;
  array dats {30} _temporary_;
  array wgts {30} _temporary_;

  by id;
  if first.id then call missing (ctr,of dats{*},of wgts{*});

  ctr+1;
  if ctr>30 then ctr=ctr-30;

  dats{ctr}=date;
  wgts{ctr}=weight;

  flag=0;
  if ctr=1 then flag=.;
  else do i=ctr-1 to 1 by -1 while(flag=0 and dats{i}+30>=date);
    if weight<= wgts{i}-32 then flag=1;
  end;
run;

[Ksharp]

data have;
input ID             Date :mmddyy10.        Weight;
format date mmddyy10.;
cards;
1              02/01/2019                              240
1              02/08/2019                              237
1              03/04/2019                              202
1              04/06/2019                              236
2              02/01/2019                              170
2              02/08/2019                              167
2              03/03/2019                              165
2              04/07/2019                              105
;

data temp;
 set have;
 by id;
 month=month(date);
 dif=abs(dif(weight));
 if first.id then call missing(dif);
run;
data want;
 set temp;
 by id month;
 if first.month and dif>32 then flag=1;
  else flag=0;
 drop month dif;
run;