## Merge

Solved
Super Contributor
Posts: 1,041

# Merge

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.

Accepted Solutions
Solution
‎09-09-2013 05:46 PM
PROC Star
Posts: 8,164

## Re: Merge

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;

All Replies
Super User
Posts: 13,556

## Re: Merge

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.

Super Contributor
Posts: 1,041

## Re: Merge

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

Thanks

Super User
Posts: 23,747

## Re: Merge

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

Super Contributor
Posts: 1,041

## Re: Merge

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;
var  unit;
id n;
run;

proc transpose data=x out=test2 prefix=date;
var  date;
id n;
run;

Super User
Posts: 23,747

## Re: Merge

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

PROC Star
Posts: 8,164

## Re: Merge

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.

Super Contributor
Posts: 1,041

## Re: Merge

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

Solution
‎09-09-2013 05:46 PM
PROC Star
Posts: 8,164

## Re: Merge

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;

🔒 This topic is solved and locked.