Do untill the condition met the parameter - Using a given Formula

[Sathish_jammy]

Hi Experts,

I need your help to resolve the given problem. Please have a look on the attachment first. 
I have a master table which contain some values (Whole dataset)

In my table I need TRV values from Master table in an order of visit dates.

I fetched First visited (fv) TRV value from Master table and I applied a formula to specify EPI value and EPI_stage using TRV.

The Formal to Define EPI and its stages: 

if (fv_cre le 0.7 and sex='F') then 
do;
	EPI = 144*(fv_cre/0.7)**-0.329 * 0.993**Age;
end;
if (fv_cre gt 0.7 and sex='F') then
do;
    EPI = 144*(fv_cre/0.7)**-1.209 * 0.993**Age;  
	end;
if (fv_cre le 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-0.411 * 0.993**Age;
end;
if (fv_cre gt 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-1.209 * 0.993**Age;
end;
if EPI ge 90 then epi_stage =1; 
if EPI ge 60 and EPI lt 90 then epi_stage =2; 
if EPI ge 30 and EPI lt 60 then epi_stage =3; 
if EPI ge 15 and EPI lt 30 then epi_stage =4; 
if EPI lt 15 then epi_stage =5; 
if EPI =. then epi_stage =.; 

The result given in second sheet of the attachment.

Here the critical part has begin,

For the Next visit (Nv) cre columns should get the values to only for [ EPI_stage (1,2) in other words EPI ge 60 ] 

For the Nv parameter the process should Checks the next visited TRV value and estimate EPI value and stages,

apply it on Nv columns only if EPI lt 60. if not again the loop should go for another visit and check the same pattern and apply the value untill last visit of IDs.

I hope you guys are grab the point. Please let me know if any clarification requires.

Any idea/solution will be appreciated.

Thanks in advance!

[Patrick]

@Sathish_jammy 

Quite a few people here in this forum will not open Excels. Best post a SAS data step which creates the data or at least attach your data as .csv

You give us the reference data (master lookup table) and the desired result but you don't provide the source sample data. Please provide these together with your full data step. This helps to better understand what you're doing and also to answer you with tested code.

[Kurt_Bremser]

See my footnotes for how to easily create a data step from a dataset. Help us to help you.

Excel files are useless for representing SAS datasets. No typing, attributes, formats for columns.

[Sathish_jammy]

Dear @Patrick  @Kurt_Bremser 

I'm sorry for the trouble.

Here I gave the sample data formats. Kindly please go-through it.

data Master;
input ID Record_Date ddmmyy10. Param_Id$ TRV;
cards;
184108 24/05/2013 CRE 1.1
187971 13/11/2013 CRE 0.9
184042 22/05/2013 CRE 0.9
187163 01/10/2013 CRE 1.3
201465 05/09/2015 CRE 1.2
187280 07/10/2013 CRE 0.6
184108 24/07/2014 CRE 2.3
187971 09/10/2017 CRE 2.5
184042 28/10/2014 CRE 1.0
187163 11/06/2014 CRE 1.2
201465 25/06/2016 CRE 1.1
187280 19/12/2016 CRE 0.7
187163 13/01/2015 CRE 1.4
201465 14/03/2017 CRE 1.4
187280 02/05/2018 CRE 1.4
;
run;
proc print data = Master;
format Record_Date ddmmyy10.;
run;
/******************************************************************/

data sub;    /*First visited values*/
input ID sex$ Age fv_DT ddmmyy10. fv_cre;
cards;
184108 M 41 24/05/2013 1.1
187971 F 60 13/11/2013 0.9
184042 F 66 22/05/2013 0.9
187163 M 47 01/10/2013 1.3
201465 M 52 05/09/2015 1.2
187280 M 48 07/10/2013 0.6
run;
proc print data = sub;
format fv_DT ddmmyy10.;
run;
/************************************************************/
data forml;
set sub;
if (fv_cre le 0.7 and sex='F') then 
do;
	EPI = 144*(fv_cre/0.7)**-0.329 * 0.993**Age;
end;
if (fv_cre gt 0.7 and sex='F') then
do;
    EPI = 144*(fv_cre/0.7)**-1.209 * 0.993**Age;  
	end;
if (fv_cre le 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-0.411 * 0.993**Age;
end;
if (fv_cre gt 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-1.209 * 0.993**Age;
end;
if EPI ge 90 then EPI_stage =1; 
if EPI ge 60 and EPI lt 90 then EPI_stage =2; 
if EPI ge 30 and EPI lt 60 then EPI_stage =3; 
if EPI ge 15 and EPI lt 30 then EPI_stage =4; 
if EPI lt 15 then EPI_stage =5; 
if EPI =. then EPI_stage =.; 
run;

/* Now using the above formula the EPI value/stage are printed, all the EPI stages are 1 and 2*/
/*So I go for the next visits of CRE values and EPI, but that results only EPI stages 3 4 5 and omit EPI stages 1 and 2*/
I attach an image contains 2 types of results. I able to get as first set but my data should be like 2nd set in an image.

In simple  :

IDs

184108, 187971, 184042 - These three IDs have (EPI_stage (3,4) on 2nd visit)

187163, 201465, 187280 - These three IDs have (EPI_stage (1,2) on 2nd visit)  but  (EPI stage(3) on 3nd visit) 

So i should skip the 2nd visit and print the 3rd visited data on the Next visited column.

I hope now it helps you people to proceed further.

[andreas_lds]

Sorry, but i still don't understand what you want as result.

[Sathish_jammy]

Dear @andreas_lds 

I ll explain it briefly in a point wise Please let me know where you need more clarification.

Point 1 : 

From the Master (Have) table, I fetched MIN record_date (First record_date) values for each IDs.

(Get the master table from above post)

So I get 6 IDs and their first record_date, CRE values are printed in my new table (Want).

Point 2 : 

In Want table I have ID(6 IDs), sex, Age, fv_date(First Record_date), fv_Cre( CRE value on First Record_date)

(Sex, Age - Updated in Want from another table).

Point 3: 

Now I have to calculate EPI value in Want table using the given formula.

(Forget about the EPI stages now that might confuse you)

data Formula;
set Want;
if (fv_cre le 0.7 and sex='F') then 
do;
	EPI = 144*(fv_cre/0.7)**-0.329 * 0.993**Age;
end;
if (fv_cre gt 0.7 and sex='F') then
do;
    EPI = 144*(fv_cre/0.7)**-1.209 * 0.993**Age;  
	end;
if (fv_cre le 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-0.411 * 0.993**Age;
end;
if (fv_cre gt 0.9 and sex='M') then 
do;
	EPI = 141*(fv_cre/0.9)**-1.209 * 0.993**Age;
end;

Once you run this. You will get first record_date EPI value in Want table.        Up to here, Want table will be look like : 

This is what I have done till now. (all First visits)

Point 4: 

Now I need to create a variable of Nxt_CRE_Date , Nxt_CRE, Nxt_EPI in Want table.

Again the above procedure follows in Nxt series but, the loop I need here is :

It should cross check the 2nd Record_date CRE value, then calculate EPI - If that EPI result is < 60 then It should print all corresponding values(Date,CRE,EPI) in  Nxt_series, Incase that EPI result is > 60 it should move and cross check the 3rd Record_date, CRE, and calculate EPI and make sure that EPI < 60 then print on same columns. If EPI not met < 60 it go to another visit. This loop goes until the condition met the last Record_date.

[Patrick · Accepted solution]

@Sathish_jammy 

It was a bit hard to understand what you have and what logic you want to implement.

Below code based on my understanding. I felt it might be easier for you if you first just calculate all the epi_stages and then in a 2nd data step select the ones you want.

data visit;
  input ID Record_Date ddmmyy10. Param_Id$ TRV;
  cards;
184108 24/05/2013 CRE 1.1
187971 13/11/2013 CRE 0.9
184042 22/05/2013 CRE 0.9
187163 01/10/2013 CRE 1.3
201465 05/09/2015 CRE 1.2
187280 07/10/2013 CRE 0.6
184108 24/07/2014 CRE 2.3
187971 09/10/2017 CRE 2.5
184042 28/10/2014 CRE 1.0
187163 11/06/2014 CRE 1.2
201465 25/06/2016 CRE 1.1
187280 19/12/2016 CRE 0.7
187163 13/01/2015 CRE 1.4
201465 14/03/2017 CRE 1.4
187280 02/05/2018 CRE 1.4
;
run;

data person;    
  input ID sex$ Age;
  cards;
184108 M 41
187971 F 60
184042 F 66
187163 M 47
201465 M 52
187280 M 48
;
run;


proc sql;
  create table person_visit as
    select 
      p.id,
      p.sex,
      p.age,
      v.record_date format=date9.,
      v.trv
    from 
      person p inner join visit v
        on p.id=v.id and Param_Id='CRE'
    order by id,Record_Date
  ;
quit;

proc format;
  invalue epi_stage
    90 - high =1
    60 -< 90  =2
    30 -< 60  =3
    15 -< 30  =4
    low -< 15 =5
    other=.
    ;
quit;

data epi_stage;
  set person_visit;
  by id Record_Date;   

  /* logic as posted - bit re-code but does logically the same */
  if sex='F' then
    do;
      if      trv le 0.7 then EPI = 144*(trv/0.7)**-0.329 * 0.993**Age;
      else if trv gt 0.7 then EPI = 144*(trv/0.7)**-1.209 * 0.993**Age;
    end;
  else if sex='M' then
    do;
      if      trv le 0.9 then EPI = 141*(trv/0.9)**-0.411 * 0.993**Age;
      else if trv gt 0.9 then EPI = 141*(trv/0.9)**-1.209 * 0.993**Age;
    end;

  EPI_stage=input(epi,epi_stage.);

run;

data epi_stage_selected;
  set epi_stage;
  by id Record_Date;   

  attrib 
    Fv_cre_DT     length=8 format=date9.
    Fv_cre        length=8
    Fv_EPI        length=8
    Fv_epi_stage  length=8
    Nv_cre_DT     length=8 format=date9.
    Nv_cre        length=8
    Nv_EPI        length=8
    Nv_epi_stage  length=8
    ;
  retain
    Fv_cre_DT	   
    Fv_cre       
    Fv_EPI	     
    Fv_epi_stage 
    Nv_cre_DT	  
    Nv_cre	    
    Nv_EPI	    
    Nv_epi_stage
    _nv_sel_flg
   ;
  keep
    id
    sex
    age
    Fv:	   
    Nv:
    ;
  Nv_Visit_selected+1;

  if first.id then
    do;
      Fv_cre_DT     = Record_Date;
      Fv_cre        = trv;
      Fv_EPI        = epi;
      Fv_epi_stage  = epi_stage;
      _nv_sel_flg=0;
      Nv_Visit_selected=1;
    end;

  /* 
    select first consecutive visit where epi_stage >2
    - if this is never the case then select last visit (if more than one visit)
  */
  else 
  if  not first.id and 
      _sel_flg = 0 and
      (epi_stage >2 or last.id)
    then 
    do;
      Nv_cre_DT     = Record_Date;
      Nv_cre        = trv;
      Nv_EPI        = epi;
      Nv_epi_stage  = epi_stage;
      _sel_flg=1;
      output;
    end;

run;

proc print data=epi_stage_selected;
run;

[Patrick]

@Sathish_jammy 

Thanks for the like but what would really help is you now telling me if the code I've posted does what you need or then explain where the logic needs to change and how.

[Sathish_jammy]

Thank you so much buddy.

It really helps me to get the exact OP.

Keep Rocking!