topic Re: Transpose in SAS Procedures

Transpose

robertrao — Mon, 06 May 2013 20:08:16 GMT

I want to transpose this dataset to get ONE Rec per :Adm-DIScharge dates

IN OUT NAME ADM_DATE DIS_DATE mrn visit_ID

25AUG2011 27AUG2011 XYZ 25AUG2011 02SEP2011 101 1

29AUG2011 31AUG2011 XYZ 25AUG2011 02SEP2011 101 2

05SEP2011 07SEP2011 XYZ 02SEP2011 30SEP2011 101 3

Thanks

Re: Transpose

PaigeMiller — Mon, 06 May 2013 20:23:40 GMT

I don't think that's possible with the example you provide. ADM_DATE of 24AUG2011 and DIS_DATE of 02SEP2011 appears twice.

Perhaps you could type in what the transposed data set should look like, maybe we can figure out how to get there.

Re: Transpose

robertrao — Mon, 06 May 2013 20:35:51 GMT

Something like this but I am worried about my VISIT_ID 2 as well???

IN OUT NAME ADM_DATE DIS_DATE mrn visit_ID in2

25AUG2011 27AUG2011 XYZ 25AUG2011 02SEP2011 101 1 29AUG2011

out2

31AUG2011

05SEP2011 07SEP2011 XYZ 02SEP2011 30SEP2011 101 3

Re: Transpose

PaigeMiller — Mon, 06 May 2013 20:43:04 GMT

This gets you part of the way there.

proc transpose data=whatever out=new;

by adm_date dis_date;

var in out;

run;

but I am worried about my VISIT_ID 2 as well

As I am not a mind-reader, would you please explain this comment?

Re: Transpose

robertrao — Mon, 06 May 2013 21:08:28 GMT

Hi,

Sory for the trouble.

My question was wrongly asked.

I want one record per VISIT_ID

IN OUT NAME ADM_DATE DIS_DATE mrn visit_ID

28OCT2011 29AUG2011 XYZ 28OCT2011 05DEC2011 101 1

01NOV2011 10NOV2011 XYZ 28OCT2011 05DEC2011 101 1

12NOV2011 25NOV2011 XYZ 28OCT2011 05DEC2011 101 1

After transposing I want all the variables and also IN A SINGLE ROW??

Re: Transpose

Haikuo — Tue, 07 May 2013 01:27:56 GMT

Thanks to OP's question, I have finally finished reading data _null_'s paper: http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

data have;

input (IN OUT) (:date9.) NAME :$ (ADM_DATE DIS_DATE) (:date9.) mrn visit_ID :$;

cards;

28OCT2011 29AUG2011 XYZ 28OCT2011 05DEC2011 101 1

01NOV2011 10NOV2011 XYZ 28OCT2011 05DEC2011 101 1

12NOV2011 25NOV2011 XYZ 28OCT2011 05DEC2011 101 1

28OCT2011 29AUG2011 XYZ 28OCT2011 05DEC2011 101 1

01NOV2011 10NOV2011 XYZ 28OCT2011 05DEC2011 101 2

12NOV2011 25NOV2011 XYZ 28OCT2011 05DEC2011 101 2

;

proc sql noprint outobs=1;

select cats(count(*)) as ct into :obs from have group by visit_id order by ct desc;quit;

proc summary nway data=have missing;

class visit_id;

output

out = want (drop=_type_ _freq_)

idgroup(out[&obs](IN OUT NAME ADM_DATE DIS_DATE mrn)=)

;

run;

Haikuo

Re: Transpose

robertrao — Tue, 07 May 2013 01:34:55 GMT

Thanks for the reply.

Could you help me this solve using Transpose.I learnt from one of our members that this can be done using two transposes.

Secondly,

2)Also if the same patient has another encounter like shown(DATES AND VISIT_id CHANGES):

WILL OUR RESULTS ALTER???????

????????tHE ONLY THING IS THAT WE GET TWO RECORDS NOW FOR MRN 101????????RIGHT??

28OCT2011    29AUG2011    XYZ     28OCT2011    05DEC2011   101     1
01NOV2011    10NOV2011    XYZ     28OCT2011    05DEC2011   101    1
12NOV2011    25NOV2011    XYZ     28OCT2011    05DEC2011   101   1

07NOV2011 9NOV2011 XYZ 06DEC2011 10DEC2011 101 2

tHANKS

Re: Transpose

art297 — Tue, 07 May 2013 05:13:18 GMT

: You are going to learn a lot faster, and save time for everyone here, if you spend more time formulating your question before you post it. I still don't know what you are trying to achieve. And, DO NOT ASK ME QUESTIONS OFF LINE! I only answer forum questions on the forum. The next time you send me a question directly, I will not respond to it on the forum.

We still can only guess as to what you really want as you haven't indicated all of the factors that one would have to consider in order to arrive at the solution you expect. Here is my guess:

data have;

informat in out adm_date dis_date date9.;

format in out adm_date dis_date date9.;

input in out name $ adm_date dis_date mrn visit_id;

cards;

28OCT2011 29AUG2011 XYZ 28OCT2011 05DEC2011 101 1

01NOV2011 10NOV2011 XYZ 28OCT2011 05DEC2011 101 1

12NOV2011 25NOV2011 XYZ 28OCT2011 05DEC2011 101 1

;

proc sort data=have out=need1;

by name adm_date dis_date mrn visit_id in;

run;

data need1;

set need1;

by name adm_date dis_date mrn visit_id;

if first.visit_id then id=1;

else id+1;

run;

proc transpose data=need1 out=need2;

by name adm_date dis_date mrn visit_id id;

var in out;

run;

proc transpose data=need2 out=want (drop=_:);

by name adm_date dis_date mrn visit_id;

var col1;

id _name_ id;

run;