Data test;
input date Body_Weight_Kg;
datalines;
1/1/2012 20
1/2/2012 .
1/3/2012 .
1/4/2012 25
1/5/2012 27
1/6/2012 .
1/7/2012 30
1/8/2012 .
1/9/2012 33
............
;
I have thousand lines of data, I want to calculate average daily weight gain. For example 1/2/2012, 1/3/2012, and 1/4/2012, WG=(25-20)/3. On 1/5/2012, WG=(27-25)/1.
Jeff,
The program will be short, but tricky. You will need to answer a few more questions to get this started.
1. Do you know how to read in dates, and store them on SAS's integer date scale?
2. Do you have additional key variables, such as an identifier of who is being weighed? I might be wrong here, but it's hard to believe that you have thousands of weight measurements, all for the same person. So if there are any other important variables, please add them to the description.
3. Is it possible that the last observation for a person will have a missing value for weight?
That should give us enough to work with.
Good luck.
If you don't have multiple ids then the following would work. It deletes all records that have missing weights and then creates the desired file. I went that route in case you were missing some dates and/or the last record(s) had missing weights:
Data have;
informat date mmddyy10.;
format date mmddyy10.;
input date Body_Weight_Kg;
datalines;
1/1/2012 20
1/4/2012 25
1/5/2012 27
1/6/2012 .
1/7/2012 30
1/8/2012 .
1/9/2012 33
;
proc sort data=have out=want;
by date;
run;
data want (drop=hold_:);
set want;
if not missing(Body_Weight_Kg);
days=max(1,dif(date));
Average_Daily_Weight_Gain=dif(Body_Weight_Kg)/days;
hold_date=date;
hold_weight=Body_Weight_Kg;
do date=hold_date-days+1 to hold_date;
if date eq hold_date then do;
call missing(Body_Weight_Kg);
end;
else Body_Weight_Kg=hold_weight;
output;
end;
run;
For your data as is, here is something you can start with:
ods _all_ close;
ods listing;
Data test;
infile cards truncover;
input date :mmddyy10. Body_Weight_Kg;
format date mmddyy10.;
datalines;
1/1/2012 20
1/2/2012 .
1/3/2012 .
1/4/2012 25
1/5/2012 27
1/6/2012 .
1/7/2012 30
1/8/2012 .
1/9/2012 33
;
data want;
set test;
retain _t _i 0;
if missing(Body_Weight_Kg) or _n_=1 then call missing(daily);
else do;
daily=(Body_Weight_Kg-_t)/_i;
call missing(_i);
end;
_t=coalesce(Body_Weight_Kg,_t);
_i+1;
drop _:;
run;
proc print;run;
Haikuo
Here is a solution which preserves the data for body weight per day and provides a result for each day. I added a dummy subject column for surely this data will have multiple subjects.
Data test;
subject = 'A' ;
Retain group count 0 ;
informat date MMDDYY10. ;
format date MMDDYY10. ;
input date Body_Weight_Kg;
If Not Missing (Lag(Body_Weight_Kg))
Then
Do ;
Group + 1 ;
Count = 0 ;
End ;
Count + 1 ;
datalines;
1/1/2012 20
1/2/2012 .
1/3/2012 .
1/4/2012 25
1/5/2012 27
1/6/2012 .
1/7/2012 30
1/8/2012 .
1/9/2012 33
;
Data test2 ;
Set test (Where = (Body_Weight_Kg >0));
By Subject Group ;
Gain = Dif(Body_Weight_Kg) ;
Gain = Gain / Count ;
Run ;
Data test3 ;
Merge test
test2
;
By Subject
Group
;
Drop Count Group ;
Run ;
Richard in Oz
I should add if you do have subject ids in your data you should add the line
If subject NE lag(subject) then group = 0 ;
after the input statement.
Also in test2 you need
If first.subject then gain = 0 ;
before the run statement.
Richard in Oz
If I understand what you mean.
Data have; informat date mmddyy10.; format date mmddyy10.; input date Body_Weight_Kg; datalines; 1/1/2012 20 1/2/2012 . 1/3/2012 . 1/4/2012 25 1/5/2012 27 1/6/2012 . 1/7/2012 30 1/8/2012 . 1/9/2012 33 ; run; proc sort data=have ; by date; run; data x; set have; retain w; if not missing(Body_Weight_Kg) then w=Body_Weight_Kg; run; data want; set x; by w notsorted; dif_w=dif(w); if w ne lag(w) then avg=dif_w/n; if first.w then n=0; n+1; run;
Ksharp
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.