topic Re: Proc Transpose in SAS Procedures

Proc Transpose

deleted_user — Fri, 17 Jul 2009 11:05:26 GMT

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

Re: Proc Transpose

ChrisNZ — Mon, 20 Jul 2009 04:02:20 GMT

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;

Re: Proc Transpose

deleted_user — Mon, 20 Jul 2009 09:14:56 GMT

Thanks for your solution.