Transpose

[robertrao]

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

[PaigeMiller]

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.

[robertrao]

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

[PaigeMiller]

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?

[robertrao]

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??

[Haikuo]

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

[robertrao]

Hi

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

[art297]

: 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;