Lapis Lazuli | Level 10

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

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

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;

``````

8 REPLIES 8
Opal | Level 21

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

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.

Super User

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

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.

Lapis Lazuli | Level 10

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

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.

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

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

Lapis Lazuli | Level 10

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

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.

Opal | Level 21

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

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;

``````

Opal | Level 21

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

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.

Lapis Lazuli | Level 10

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

Thank you so much buddy.

It really helps me to get the exact OP.

Keep Rocking!

Discussion stats
• 8 replies
• 1265 views
• 1 like
• 4 in conversation