Help using Base SAS procedures

Merge

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

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: 7,474

Re: Merge

Posted in reply to robertrao

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=_Smiley Happy;

  by ID in out;

  var  col1;

  id _name_ n;

run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Merge

Posted in reply to robertrao

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: 19,820

Re: Merge

Posted in reply to robertrao

That depends on your 'definition' of simple Smiley Happy

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

Super User
Posts: 19,820

Re: Merge

Posted in reply to robertrao

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.

PROC Star
Posts: 7,474

Re: Merge

Posted in reply to robertrao

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: 7,474

Re: Merge

Posted in reply to robertrao

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=_Smiley Happy;

  by ID in out;

  var  col1;

  id _name_ n;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 288 views
  • 6 likes
  • 4 in conversation