How to categorize variable in one dataset based on range reference in another dataset?

[sascool]

Hi everyone,

I came across this question in my working project. It does not seem to be a difficult task, but I just can not figure it out after spending the whole night, sigh....:smileyconfused:

There are two datasets, one contains patients ID and one continuous biomarker variable x, the other dataset provides the reference range of this biomarker x (lowref_x and highref_x) and corresponded disease severity score for each range of x. Since there is no common variable between the two datasets I have to come up with some coding to generate the disease severity score for each patient ID based on his marker value in dataset one and reference range in dataset two. Can anyone help me out on this? I really appreciate it!!

[stat_sas]

Hi,

One way is to calculate range in both datasets and use this variable as a common variable to combine them. Provided that there is an exact relationship between ranges in both datasets.

Thanks

Naeem

proc sql;
create table a as
select id,range(x) as r from one
group by id;
quit;

data b;
set two;
r=highref_x-lowref_x;
run;

proc sql;
select a.id,a.r,b.severity from a
inner join b
on a.r=b.r;
quit;

[ballardw]

Another could be to use the reference data to create custom formats then use the appropriate format for any print or report procedures.

The specifics on using a data set to create a format are in reference for Proc Format and the CNTLIN option.

A manual example:

proc format;

value marker

1 - 10 = 'Low'

10<-20 = 'Medium'

20<-high= 'High';

run;

data test;

     input id x;

datalines;

1 4.5

2 11.7

3 205.6

;

run;

proc print data=test noobs;

     var id x;

     format x marker.;

run;