Merge

[robertrao]

Can someone explain to me step by step what the following code is doing to the table X

I am confused

Thanks

TABLE X:

ID        IN                       OUT            date          Unit    max   n
1    06/01/1989        06/06/1989    06/01/1989   North   1      1
1    06/01/1989        06/06/1989    06/05/1989   South  2       2
1    06/01/1989        06/06/1989    06/06/1989   West   3      3
1    06/07/1989        06/20/1989    06/08/1989   East   3      1

%macro mer;

data table3(drop=n max);

merge

%do i=1 %to &max;

x(rename=(date=date&i unit=unit&i) where=(n=&i))

%end;

;

by id;

output;

call missing(of _all_);

run;

%mend mer;

options mprint;

%mer

/*USING MPRINT I COULD NOT FIGURE OUT WHATS GOING ON*/

MPRINT(MER):   data table3(drop=n max);
MPRINT(MER):   merge x(rename=(date=date1 unit=unit1) where=(n=1)) x(rename=(date=date2 unit=unit2) where=(n=2)) x(rename=(date=date3 unit=unit3) where=(n=3)) ;
MPRINT(MER):   by id;
MPRINT(MER):   output;
MPRINT(MER):   call missing(of _all_);
MPRINT(MER):   run;

NOTE: There were 2 observations read from the data set WORK.X.
      WHERE n=1;
NOTE: There were 1 observations read from the data set WORK.X.
      WHERE n=2;
NOTE: There were 1 observations read from the data set WORK.X.
      WHERE n=3;
NOTE: The data set WORK.TABLE3 has 2 observations and 9 variables.

[ballardw]

It isn't doing anything to table X. It is taking some variables and aligning them soe that different rows have been combined by ID sot that the date and unit from 3 records are on one. A form of transpose basically.

[robertrao]

Thanks for the reply. is there a simple way to do this??

Thanks

[Reeza]

That depends on your 'definition' of simple

For some people a macro is more simple. Since your max fluctuates (I'm guessing) its a problem that needs a dynamic solution.

A couple of different ways to do this are:

1. Multiple proc transposes and merge the results together.

2. A transpose via datastep

UCLA has tutorials on either methods:

SAS Learning Module: Reshaping data long to wide using the data step

SAS Learning Module: How to reshape data long to wide using proc transpose

[robertrao]

hi,

Thanks so much. I understand now that i need to transpose to get the same reult

by ID ADMIT Disch: to get the result

I transpose twice and merge them?is that the right method??

proc transpose data=x out=test prefix=unit;
by ID Admit Disch;
var  unit;
id n;
run;

proc transpose data=x out=test2 prefix=date;
by ID Admit Disch;
var  date;
id n;
run;

[Reeza]

The right method is the one that gets you the right answer.

You need to test, we can't answer that. Although your code may be correct, it may not answer your question.

[art297]

First, your &max value isn't even defined as part of the macro.  Given your results, I presume that is was set at 3.

I agree with that it is doing a form of transpose, but my guess is that the east n and max values, and &max, were all supposed to be set at 4.  Then it would do a transpose.

[robertrao]

Hi Art,

I beleive 3 is right because we want it by Adm-disch dates

even though the 4 records are for the same patient they are 2 different adm-disch !!!

Thanks

[art297 · Accepted solution]

If you are trying to do what I think you are trying to accomplish, I think the following double transpose will come closer:

data X;

  informat in out date mmddyy10.;

  format in out date mmddyy10.;

  informat unit $5.;

  input id in out date unit max n;

  cards;

1    06/01/1989        06/06/1989    06/01/1989   North   1      1

1    06/01/1989        06/06/1989    06/05/1989   South  2       2

1    06/01/1989        06/06/1989    06/06/1989   West   3      3

1    06/01/1989        06/20/1989    06/08/1989   East   3      1

2    06/01/1989        06/06/1989    06/01/1989   North   1      1

2    06/01/1989        06/06/1989    06/05/1989   South  2       2

2    06/01/1989        06/06/1989    06/06/1989   West   3      3

3    06/01/1989        06/06/1989    06/01/1989   North   1      1

3    06/01/1989        06/06/1989    06/05/1989   South  2       2

3    06/07/1989        06/20/1989    06/08/1989   East   3      1

;

proc transpose data=x out=test;

by ID in out n;

var date unit;

run;

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

  by ID in out;

  var  col1;

  id _name_ n;

run;