Help using Base SAS procedures

Proc Transpose

Reply
N/A
Posts: 0

Proc Transpose

I have a wide data set of hospital lab test results. The data contains ID, hospital admit date, lab results date, name of the test and result (character).

I have a routine that cleans the data and creates a numeric value for each test; if the data is invalid, a missing value is generated.

What I need Proc Transpose (or some other routine) to do is create a new record for each test. The problem I am having is I want the character and numeric test result on the same row for that test.

For example, if I have a test name for glucose, I will have a character glucose result and a numeric glucose result (char value = -40, numeric value = .)

The row would contain ID, hospital admit date, lab results date, glucose_char and glucose (numeric value). Next row would contain ID, hospital admit date, lab results date, wbc_char and wbc(numeric value).

How can I do this in Proc Transpose or some other way?

Thanks for your help.
Dave
PROC Star
Posts: 1,564

Re: Proc Transpose

You don't want to transpose as all your column data remains in columns.

You want multiple output rows per input row. This can be done by using several output statements in a data step;
[pre]
data IN;
ID='1'; hospital='a'; admit_date='01jan2000'd; lab_date='01jan2000'd;
TEST1='gluc'; RESULTC1='-40';RESULTN1=2;
TEST2='chol'; RESULTC2='208';RESULTN2=45;
TEST3='urea'; RESULTC3='4'; RESULTN3=415;
run;

data OUT;
length TEST RESULTC $20 RESULTN 8;
drop TEST1 RESULTC1 RESULTN1
TEST2 RESULTC2 RESULTN2
TEST3 RESULTC3 RESULTN3;
set IN;
TEST=TEST1; RESULTC=RESULTC1; RESULTN=RESULTN1; output;
TEST=TEST2; RESULTC=RESULTC2; RESULTN=RESULTN2; output;
TEST=TEST3; RESULTC=RESULTC3; RESULTN=RESULTN3; output;
run;
N/A
Posts: 0

Re: Proc Transpose

Thanks for your solution.
Ask a Question
Discussion stats
  • 2 replies
  • 122 views
  • 0 likes
  • 2 in conversation