How to Derive a weight based on previous weight .

[Balaji13]

Hi, 

I have below data where i need to create a variable Derived weight (Derived_wt) based on the percentage difference between two weights.

In below data subject is having the corresponding weight (Current_wt) for each visit. 

1) For first visit we need to find out percentage difference between Current_wt and Base_wt. If percentage difference is within 10 % then we should populate Derived_wt = Base_wt. Else we should populate Derived_wt = Current_wt.

2) From visit = 2, the scenario changes as :

     for second visit I need to populate Base_wt  = Derived_wt (Which is derived in previous step). Then i have to find out the percentage difference between Current_wt and base_wt, again if difference is within 10% then we have to populate Derived_wt =Base_wt. 

Else Derived_wt=Current_wt. 

3) For Visit =3, now Base_wt =Derived_wt(Which is Derived in previous step). 

Then I have to find out the percentage difference between Current_wt and base_wt, again if difference is within 10% then we have to populate Derived_wt =Base_wt. 

Else Derived_wt=Current_wt. 

This should be done for all the subjects in data.

I Could able to derive for first visits for all subjects .  

Could anyone please help me to derive weight for all visits for all subjects. Below is the sample data for your understanding.

Thank you in advance

Subject	visit	Current_wt	Base_wt	Within 10% from Previous wt	Derived_wt
1	1	78	80	Yes	80
1	2	79		yes	80
1	3	70		no	70
1	4	72		yes	70
1	5	74		yes	70
1	6	88		no	88
1	7	71		no	71
2	1	52	50	yes	50
2	2	58		no	58

[Shmuel]

Please post your code and we shall help yo to accomplish it.

Use either LAG() function or RETAIN statement to hold previous line data.

Use BY statement to enable IF FIRST.SUBJECT checking.

Something like:

data want;
  set have;
    by subject;   /* assuming data is ordered by subject visit */
         retain save_base_wt;
         if first.subject then save_base_wt = base_wt;
         if visit=1 then derived_wt = save_BASE_WT;
         else do;
              ...........
         end;
run;       

[Balaji13]

Hi Shmuel,

Thank you for your reply. Attached my code for your reference. 

I am able to derive the derived weight for Visit 1 and 2. However, from visit 3 my loop is not working. Could you please help me.

[Shmuel]

Is there a reason that you prefer check first.subject instead check visit number ?

Can subject start with visit  not equal to 1 ?

I see no reason for two steps (data test1; data test2;) - I believe it can be done in one step.

When you use a retained variable its value is saved until you change it.

On any obs that is not first.subject you do call missing(bwt);

therefore, for each such obs you will get n(cwt, bwt) = 1 - and you did not supply code for it.

try next code:

/*
Cwt is current weight
Bwt is base weight. 
Dwt is Derived weight.
*/

data test;
input subject visit cwt bwt 8.;
datalines;
1 1 78 80
1 2 79 .
1 3 70 .
1 4 72 .
1 5 74 .
1 6 88 .
1 7 70.
2 1 51 50
2 2 58 .
;
run;

proc sort data=test;
  by subject visit;
run;

data test1;
  set test;
    by subject;   
         retain save_bwt dwt;
         if first.subject then save_bwt = bwt;
         if visit=1 then dwt = save_bwt;
         else do;
            if n(cwt, save_bwt) = 2 then pdiff = round((cwt - save_bwt)*100/save_bwt,0.1);
            if pdiff <= 10 then dwt = save_bwt; 
			   else dwt = cwt;
         end;
       drop save_bwt;  
run;   

[Balaji13]

From the above code, i am getting the attached output.

Derived weight(dwt) 80 is same for all visit for subject 1. When the pdiff is more than 10%.  However, i want to display dwt =cwt when pdiff is more than 10%. 

For subject 2 it is working for visit 2. I am not sure, why it is not working for subject 1. 

To answer your questions,

1) In real data, i will sort with subject and visit and take the first.subject as first observation for each subject. 

2) No, subject will not start visit not equal to 1.

[Shmuel]

Just change a line:

 if abs(pdiff) <= 10 then dwt = save_bwt; 
	             else dwt = cwt;

Pay attention to ABS() function.

[Shmuel]

To get results as in your first post the code should be (checking for < not for <=):

if abs(pdiff) < 10 then dwt = save_bwt;
else dwt = cwt;

[Balaji13]

HI,

 I really appreciate your time. Thanks a lot. There is a little catch.

As explained in my first post, 

For visit 1, we should first find the pdiff between cwt and bwt. If pdiff within 10% then dwt =bwt. Else dwt=cwt. 

 From visit 2 onwards, bwt should be equal to lag( dwt ) (derived in previous step). Now we have to find out the difference between cwt and dwt. If pdiff is within 10% then we should populate dwt =lag(dwt). if pdiff is more than 10% then dwt =cwt. 

for visit 3, now bwt = lag(dwt) and repeat the above steps.

example with required output is in my first post. 

attached the output dataset for your reference. 

[Shmuel · Accepted solution]

Pay attention - there is a slight change in data: subject-visit 1-7 and 2-1.

I have run the next code with data as in your first post and got exactly the expected results:

/*
Cwt is current weight
Bwt is base weight. 
Dwt is Derived weight.
*/

data test;
input subject visit cwt bwt 8.;
datalines;
1 1 78 80
1 2 79 .
1 3 70 .
1 4 72 .
1 5 74 .
1 6 88 .
1 7 71.
2 1 52 50
2 2 58 .
;
run;

proc sort data=test;
  by subject visit;
run;

data test1;
  set test;
    by subject;   
         retain save_bwt;  
         /* lag() function should be used unconditionally ! */
         if first.subject then do;
            pdiff = round((cwt - bwt)*100/bwt,0.1);
            if abs(pdiff) < 10 then dwt = bwt; 
	                       else dwt = cwt; 
	    save_bwt = dwt; /* for visits 2 */  
	    output;
	 end;
         else do; /* for visit 2 and following */
            if n(cwt, save_bwt) = 2 then pdiff = round((cwt - save_bwt)*100/save_bwt,0.1);
            if abs(pdiff) < 10 then dwt = save_bwt; 
			       else dwt = cwt;
	    save_bwt = dwt; /* for next visits */  
	    output;			   
         end;
       *drop save_bwt;  /* unmark to drop this variable from the output */
run;   

[Balaji13]

Thanks a lot  Shmuel, It is working in test data. Hope it should work in real time data.