Create new lab variables based on day-of-life

[GAL1986]

Hi everyone, 

I am trying to manipulate lab following data and create individual lab variables based on baby day-of-life that the lab test was performed. 

In the following data, Bilirubin is a good example of what I am trying to manipulate, though I want to do this for all lab results. 

baby_id	lab_name	lab_result	lab_reference_range	Day_of_life
123	AMINO ACID DISORDERS	IN RANGE	Within normal range	2
123	BILIRUBIN TOTAL	10.0	Within normal range	3
123	BILIRUBIN TOTAL	7.9	Within normal range	2
123	BIOTINIDASE DEFICIENCY	>30	Within normal range	2

I want the following variables in the final dataset

bilirubin_reference_2 (for day-of-life 2)=1 (1=Normal range) 

bilirubin_result_2=7.9 

bilirubin_reference_3=1

bilirubin_result_3=10

I am trying to do this for a very large dataset and I can't figure out how to get this done in one step. Any help would be appreciated! 

Thanks

[ballardw]

---

@GAL1986 wrote:

Hi everyone, 

I am trying to manipulate lab following data and create individual lab variables based on baby day-of-life that the lab test was performed. 

In the following data, Bilirubin is a good example of what I am trying to manipulate, though I want to do this for all lab results. 

baby_id	lab_name	lab_result	lab_reference_range	Day_of_life
123	AMINO ACID DISORDERS	IN RANGE	Within normal range	2
123	BILIRUBIN TOTAL	10.0	Within normal range	3
123	BILIRUBIN TOTAL	7.9	Within normal range	2
123	BIOTINIDASE DEFICIENCY	>30	Within normal range	2

I want the following variables in the final dataset

bilirubin_reference_2 (for day-of-life 2)=1 (1=Normal range) 

bilirubin_result_2=7.9 

bilirubin_reference_3=1

bilirubin_result_3=10

I am trying to do this for a very large dataset and I can't figure out how to get this done in one step. Any help would be appreciated! 

Thanks

---

From the contents of your data set , i.e values of variables that exist now, how do we tell which is a bilirubin_reference or bilirubin_result variable?

What is supposed to happen with the other variables in the data set? Is your current lab_result variable character or numeric? What happens with that '>30' value?

Best is to provide example data in the form of data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

If possible provide something similar for the result.

[GAL1986]

hi, 

Thanks for the response, hope this clarifies things. 

From the contents of your data set , i.e values of variables that exist now, how do we tell which is a bilirubin_reference or bilirubin_result variable? By the variable names listed in the original data I provided.... lab_result will turn into the NAMEofLAB_Result, in this case, Bilirubin_Result and the lab_reference_range to NameofLab_reference. 

I need to go through and create the reference ranges myself based on the numeric lab values like follows

if lab name =' BILIRUBIN TOTAL' and 12>=LAB_RESULT2>=0 then lab_reference_range=1

Where 1="within normal range". If I can get code to do this without having to type it out the long way, that would be great

What is supposed to happen with the other variables in the data set? Is your current lab_result variable character or numeric? What happens with that '>30' value? . I have turned them into numeric values where I could. The >30 won't be coded because I know it's not numeric. Pretend they are all numeric for this example. 

data WORK.TEST_FOR_HELP;
infile datalines dsd truncover;
input lab_name:$59. baby_id:BEST12. lab_result:$302. Day_of_life:32.;
format baby_id BEST12.;
datalines;
AMINO ACID DISORDERS 268856296 IN RANGE 2
BILIRUBIN TOTAL 268856296 7.9 2
BILIRUBIN TOTAL 268856296 10.0 3
BIOTINIDASE DEFICIENCY 268856296 >30 2
CONGENITAL ADRENAL HYPERPLASIA 268856296 7 2
;;;;

[GAL1986]

Also, here is some very long code that I know gives me what I need, but I am trying to condense as I have to do this for over 100 lab variables

The following code gives me these variables 

HYDROXYPROGESTERONE_1-HYDROXYPROGESTERONE_1 where 1=normal range 0=Low and 2=high

and HYDROXYPROGESTERONE_VALUE_1-HYDROXYPROGESTERONE_VALUE_7 which equal the actual numeric lab result variable. The _number at the end of the variables indicate the day-of-life at which the lab test was performed.

I am trying to condense this code and I just can't figure out how to do it! 

IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=1 THEN HYDROXYPROGESTERONE_1=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=2 THEN HYDROXYPROGESTERONE_2=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=3 THEN HYDROXYPROGESTERONE_3=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=4 THEN HYDROXYPROGESTERONE_4=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=5 THEN HYDROXYPROGESTERONE_5=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life=6 THEN HYDROXYPROGESTERONE_6=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND 106>=LAB_RESULT2=>7 and day_of_life>6 THEN HYDROXYPROGESTERONE_7=1/*NORMAL*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=1 THEN HYDROXYPROGESTERONE_1=0/*LOW*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=2 THEN HYDROXYPROGESTERONE_2=0/*LOW*/;
IF LAB_NAMEIN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=3 THEN HYDROXYPROGESTERONE_3=0/*LOW*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=4 THEN HYDROXYPROGESTERONE_4=0/*LOW*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=5 THEN HYDROXYPROGESTERONE_5=0/*LOW*/;
IF LAB_NAMEIN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life=6 THEN HYDROXYPROGESTERONE_6=0/*LOW*/;
IF LAB_NAMEIN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2<7 and day_of_life>6 THEN HYDROXYPROGESTERONE_7=0/*LOW*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=1 THEN HYDROXYPROGESTERONE_1=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=2 THEN HYDROXYPROGESTERONE_2=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=3 THEN HYDROXYPROGESTERONE_3=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=4 THEN HYDROXYPROGESTERONE_4=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=5 THEN HYDROXYPROGESTERONE_5=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life=6 THEN HYDROXYPROGESTERONE_6=3/*HIGH*/;
IF LAB_NAME IN ('17-HYDROXYPROGESTERONE') AND LAB_RESULT2>106 and day_of_life>6 THEN HYDROXYPROGESTERONE_7=3/*HIGH*/;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_1=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_2=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_3=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_4=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_5=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_6=lab_result2;
if HYDROXYPROGESTERONE_1 in (0,1,2) then HYDROXYPROGESTERONE_VALUE_7=lab_result2;