Wide to Long Format For Data

shellp55 — Fri, 24 Jun 2011 15:14:10 GMT

Hello

I am using SAS 9.1. I have a hospital data set where one abstract is one row of data.

I was able to figure out how to extract the multi-occurring fields like diagnosis or procedure using the following:

DATA have;

set mylib.data_2011;

keep chartno acctno sepdate diagcde1-diagcde25;

RUN;

proc sort data=have out=have_sort;

by acctno chartno;

run;

proc transpose data=have_sort out=need(rename=col1=dxcode);

by acctno chartno sepdate;

var diagcde1-diagcde25;

run;

data Dx_List;

set need;

format sepdate ddmmyy10.;

where dxcode ne "";

run;

With hospital there are 25 possible diagnoses that could be coded but not every patient will have 25 diagnoses but in the transpose step it assigns diagcde1-diagcde25 for all cases. My last step for Dx_List is to remove the blanks and everything is working well.

But how do I introduce other variables? For instance, I want to also include diagtyp, diagpre, and diagclust which all also have 25 possible entries.

Also, for the code above, it creates a column called "Name of Former Variable" which lists diagcde1-diagcde25 - how do I change that field? For instance, I would like it to show as occurrence number instead of this text field. In other words take out the diagcde portion of the field to leave the #.

Any and all assistance greatly appreciated - thanks.

Wide to Long Format For Data

DBailey — Fri, 24 Jun 2011 15:28:11 GMT

I may not be understanding your desired output and I certainly don't understand your data, but if you'd like to have one row per dx, you might try something like this:

data Dx_List (keep=chartno acctno sepdate diagtyp diagpre diagclust diagcde);

set mylib.data_2011;

array dtyp(25) diagtyp1 - diagtyp25;

array dpre(25) diagpre1 - diagpre25;

array dclust(25) diagclust1 - diagclust25;

array dcde(25) diagcde1 - diagcde25;

do j = 1 to 25;

if dcde(j) >'' then do;

diagtype=dtype(j);

diagpre = dpre(j);

diagclust=dclust(j);

diagcde = dcde(j);

output;

end;

run;

topic Wide to Long Format For Data in SAS Health and Life Sciences

Wide to Long Format For Data

Wide to Long Format For Data