**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.
@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.
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.
@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.
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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.