proc transpose date help

[tmm]

I have a proc transpose statement and I want to add the length of stay into the final output.

My table I am transposing from has the following columns:

memid      fstsrvdt            admitdt        dischdt                lengthofstay

1111        27oct2010      27oct2010    30oct2010                  3

2222        04dec2009      04dec2009  08dec2009                  4

2222        18dec2010      18dec2010  23dec2010                  5

3333        06dec2010      06dec2010  06dec2010                  0

3333        06dec2010      06dec2010  08dec2010                  2

I want to do this because when the dates are transposed I want to then go back and say anything that is 0 legnth of stay is not included in the final analysis when I am doing a case statement on my information. The code I have for the transpose is:

PROC TRANSPOSE DATA=ReAdm.Cleaning OUT = ReAdm.AdmDT (DROP=_NAME_ _LABEL_) PREFIX = ADMITDT;

BY memid;

VAR ADMITDT;

RUN;

I thought I could put by memid lengthofstay but that does not work.

[Haikuo]

If you could layout a wanted output, that would be a lot easier for us.

Regards,

Haikuo

[tmm]

The output right now is:

memid     admitdt1   admitdt2    admitdt3      admitdt4 .......................................admitdt12

I want

memid    admitdt1.........................................................admitdt12     lengthof stay

The reason is when I merge the admitdt table with the dischdt table, I end up with the memid follwed by 12 admitdates and 12 dischdates. The dates are

duplicated in many cases because of the length of stays. So in my above example, the memid 3333 is on 1 row but because 1 has a length of stay of 2 and the other 0 the dates get duplicated. I need to count both in my overall because it is part of my denominator when doing my calc later on but I do not need it in the numerator portion. So if I could do my transpose to include the length of stay then in my next proc sql I can do my code the way I need to in order to pull out my numerator.

[Astounding]

tmmm,

It looks like you have some confusion about the end result here.  There is no such thing as a single length-of-stay that applies to the entire MEMID.  You would need 12 lengths of stay, one for each ADMITDT. 

While you could think about adding LengthOfStay to the VAR statement in PROC TRANSPOSE, it's simpler to eliminate the records entirely from your analysis data set.  Add this statement to your PROC TRANSPOSE step:

where lengthofstay > 0;

You'll be left with just the records you need to analyze.  If (as you say) your SAS skills are just coming up to speed,  you might even save the transposed data twice ... one as it is now, and once after adding the WHERE statement.  It depends on what you need to do with the data later. 

Good luck.

[tmm]

I will have to see how to work this out. I need to count in my final output those that have this lenght of stay 0 to get my national level. The end result is a chisquare where the national level right now equals 85391 because I did a the whole code already and that should be the end national level. the national is the denominator and counts the number of distinct memid's. The numerator are the taxid's associated with those memid's and are those that are readmitted after an initial admission. so if the memid was admitted jan12009 and discharged jan32009 and then the 2nd admit is jan152009, this counts as readmission and it does not matter if they had 100 more after that. It is simply a yes they were. but then it counts it at a tax id level. so for these 85391 memid's they link up to 4975 distinct taxid's. when i did my program prior i got 232 readmission counts and was told no way. that is wrong. it has to be more like 1300. so i went back to my code and there is something wrong with how after i transpose the admitdt and dischdt and then work my case statements when it is looking at say if admitdt1 =admitdt2 or admitdt2 is null and all the case stuff i have done. so i thought i had to go back to maybe prior to my transpose and see if the length of stay might be affecting my outcome and start from there. i cannot exclude the 0's in the length of stay cuz they go into that overall national count of 85391.

[sassharp · Accepted solution]

A SAS® Macro for Transposing Large Data sets

http://www.nesug.org/Proceedings/nesug11/cc/cc20.pdf

[tmm]

Macros are too difficult for me. I am new to SAS. I just need to know how to keep my variable length of stay.