BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
robertrao
Quartz | Level 8

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.




1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

8 REPLIES 8
ballardw
Super User

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
Quartz | Level 8

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

Thanks

Reeza
Super User

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

robertrao
Quartz | Level 8

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
Super User

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
Opal | Level 21

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
Quartz | Level 8

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
Opal | Level 21

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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