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
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;
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!
Thanks for the tips! I hope my question is easier to understand now
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;
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.
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.
Your problem is loosely defined, so there is probably extra code below.
This program:
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.