BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
2 REPLIES 2
ChrisNZ
Tourmaline | Level 20
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;
deleted_user
Not applicable
Thanks for your solution.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 713 views
  • 0 likes
  • 2 in conversation