Wishing to Evaluate Lab Test Data by Test Type and Descriptive Summary of Results for each Test Type

[SAS_Novice22]

**EDITED FOR CLARITY**

Hi SAS Experts,

I would like to analyze lab data from a small number of samples. My data looks like this:

SUBID	LBTESTCD	LBTESTCD_DEC	LBORRES	LBORRESU_DEC	LBORNRLO	LBORNRHI
0001	4	C4	6	mg/dL	14	44
0006	1	C1-INH antigenic levels	10	mg/dL	19	37
0004	1	C1-INH antigenic levels	4	mg/dL	21	39
0008	4	C4	<2	g/L	10	40
0002	2	C1-INH functional levels	.08	% of normal	67	100
0002	3	C1q	28	mg/dL	12	22
...	...	...	...	...	...	...

I would like that data to look like this:

		Test Results	-->	-->	-->	-->	-->
Test Name	Number of Tests	Mean (SD)	Median	Min	Max	Kurt	Skew
C1-INH antigenic levels (units)	N=
C1-INH functional levels (units)	N=
C4 (units)	N=
C1q (units)	N=

Each row in the dataset represents a lab test and as such subjects can have multiple rows. This does not matter to me because I am looking for basic descriptive on the number of tests performed for each test type and the associated descriptive values of test results for each test type.

I am looking at the raw dataset and I see that within the same test type there are a variety of units for the test result displayed (e.g., mg/dL, g/L, %. % of normal), I am assuming I would first have to start off with cleaning the data to make the units all the same?

So would the steps I take look like:

1. clean the raw dataset and convert all values for each result to be consistent for each test type

2. group each lab test type into its own category - would I generate a new variable or use if and then statements?

3. run the following for each lab test?

4. Would this also include the low and high reference ranges?

PROC IMPORT OUT = LB
    DATAFILE = 'M:\SAS\TAKEDA EDR/LB.xlsx'
    DBMS = xlsx;
RUN;

DATA LB2;
    SET LB;
    OVERALL = "Overall";
RUN;

TITLE "Type of Lab Test Name - how to clean data, convert units, therafter use this data to calculate means etc. and include ref ranges??"; 
PROC FREQ DATA=WORK.LB2;
    TABLE LBTESTCD_DEC * OVERALL;
RUN;

DATA WORK.LB2;
SET LB;
IF LBTESTCD_DEC="C1-INH functional levels" THEN C1INH_F=1;
ELSE C1INH_F = .;
RUN;

TITLE "Lab Results by Lab Test Type"; 
PROC MEANS DATA=WORK.LB2 MEAN STDDEV MEDIAN MIN MAX KURT SKEW;
	CLASS OVERALL;
	VAR insert test name here;
RUN;

Thank you in advance for your assistance.

Best wishes,

T.

[PaigeMiller]

I am looking at the raw dataset and I see that within the same test type there are a variety of units for the test result displayed (e.g., mg/dL, g/L, %. % of normal), I am assuming I would first have to start off with cleaning the data to make the units all the same?

I don't understand why you say this, as your desired output table does not have any column for units. But even more confusingly, you want a mean in your output table, but you don't indicate what variable you want to use to calculate the mean.

So I feel there are major disconnects between your input data, your description in words of what you want to do, and the desired output table.

[SAS_Novice22]

@PaigeMiller - thank you, sorry I should have been more clear, the descriptive stats (mean, median, min, max, skew, kurtosis) were to showcase the test results. I will try now to edit my post.

Thanks for pointing this out.

T.

[PaigeMiller]

Typing "Test results" above the word Mean still does not indicate which of the many variables you are computing a mean for.

2. group each lab test type into its own category - would I generate a new variable or use if and then statements?

3. run the following for each lab test?

PROC SUMMARY or PROC MEANS seems to be all you would need to do, once all the other issues are cleared up.

[ballardw · Accepted solution]

---

@SAS_Novice22 wrote:

So would the steps I take look like:

1. clean the raw dataset and convert all values for each result to be consistent for each test type

2. group each lab test type into its own category - would I generate a new variable or use if and then statements?

3. run the following for each lab test?

4. Would this also include the low and high reference ranges?

Basically it looks like you have the right idea. First, would be the make sure that you have numeric variables for anything that you want to do statistics like Mean, Median, Max etc. When you show a value such as <2 that variable as it currently exists is very likely to not be numeric and you need to decide what value will be recorded. For statistics like Mean to be useful the units must be the same. Typically if variables represent different units of measure the approach is to make a variable for each.

If you just want a REPORT, once you have consistent numeric values you may be able to use the structure you have with some thing like:

proc tabulate data=have;
   class LBTESTCD_DEC;
   var LBORRES;
   table  LBTESTCD_DEC,
         lborres*( n mean std min max skew kurt)
   ;
run;

For other analysis you may be able to use your lab test variable as a CLASS variable (Proc Means for example) or a BY variable (sort the data by the variable first).

SAS makes it fairly easy to do many tasks by values of grouping variables (By group processing for instance with variables on a BY statement)

4. Would this also include the low and high reference ranges?

Depends on what you mean by "include" and what you want to do with other variables. Also, with questions please use VARIABLE names to reference things. When we have to guess what a "reference range" would be suggestions may well be quite different than you want. You show 2 variables but no "range" I guess are high and low somethings. So those might be limits but how they could be used you need to provide examples or descriptions.

You can request statistics for any numeric variable.

[SAS_Novice22]

Thanks ballardw. This is useful that is exactly how I will proceed. Thank you for the suggestion. I will clean the data first to make sure all measures of units are the same for each of the 4 different lab tests.

Thank you again for the solution to my problem.

T. 🙂