topic Re: Change in weight every 30 days in SAS Programming

Change in weight every 30 days

jmmedina25 — Fri, 22 Mar 2019 16:56:56 GMT

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

Re: Change in weight every 30 days

novinosrin — Fri, 22 Mar 2019 14:53:59 GMT

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!

Re: Change in weight every 30 days

VDD — Fri, 22 Mar 2019 22:15:30 GMT

~~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.

Re: Change in weight every 30 days

jmmedina25 — Fri, 22 Mar 2019 16:57:58 GMT

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

Re: Change in weight every 30 days

PaigeMiller — Fri, 22 Mar 2019 17:42:00 GMT

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.

Re: Change in weight every 30 days

mkeintz — Fri, 22 Mar 2019 18:03:14 GMT

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

This program:

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.
clear the array every time you encounter a new id. ("if first.id then call missing(.......);");
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.
Initialize the flag to missing for first record of an ID, or else to zero.
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;

Re: Change in weight every 30 days

novinosrin — Fri, 22 Mar 2019 18:15:39 GMT

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;

Re: Change in weight every 30 days

Ksharp — Sat, 23 Mar 2019 12:26:14 GMT


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;