topic Re: Merge in SAS Procedures

Merge

robertrao — Mon, 09 Sep 2013 19:56:59 GMT

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.

Re: Merge

ballardw — Mon, 09 Sep 2013 20:39:09 GMT

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.

Re: Merge

robertrao — Mon, 09 Sep 2013 20:48:41 GMT

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

Thanks

Re: Merge

Reeza — Mon, 09 Sep 2013 20:53:00 GMT

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

Re: Merge

robertrao — Mon, 09 Sep 2013 21:12:28 GMT

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;

Re: Merge

art297 — Mon, 09 Sep 2013 21:14:16 GMT

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.

Re: Merge

Reeza — Mon, 09 Sep 2013 21:21:55 GMT

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.

Re: Merge

robertrao — Mon, 09 Sep 2013 21:32:07 GMT

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

Re: Merge

art297 — Mon, 09 Sep 2013 21:46:30 GMT

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;