How to transpose records to variables based on one variable

[c8826024]

Bear with me if this question has been resolved but I can't find my answer by searching this forum. I have a dataset like this:

id     visit_date

001     1/1/2011

001      3/20/2011

001      4/15/2011

002      8/1/2011

002      9/2/2011

003      1/23/2011

003     2/2/2011

003     2/15/2011

003    3/8/2011

003    3/15/2011

003     3/28/2011

003     3/29/2011

003    3/31/2011

.........

The final dataset I want is like this:

id     visit1     visit2     visit3     visit4     visit5      visit6      visit7      visit8       visit9  visit10....         

001   1/1/11   3/20/11  4/15/11

002   8/1/11  9/2/11

003  1/23/11  2/2/11  2/15/11  3/8/11  3/15/11   3/28/11   3/29/11   3/31/2011 ...

I don't know how many times the patiences have been visit. But I just want to have one record/patience with all the visit dates populated. I know I should use ARRAY but I don't know how to impletment it. Can anyone help?

Thanks.   

[art297]

That is very easy using proc transpose.  e.g., as long as your data is already sorted by id visit_date, then the following should do what you want:

data have;

  informat id $3.;

  informat visit_date mmddyy10.;

  format visit_date mmddyy10.;

  input id     visit_date;

  cards;

001     1/1/2011

001      3/20/2011

001      4/15/2011

002      8/1/2011

002      9/2/2011

003      1/23/2011

003     2/2/2011

003     2/15/2011

003    3/8/2011

003    3/15/2011

003     3/28/2011

003     3/29/2011

003    3/31/2011

;

proc transpose data = have out = want prefix = visit;

  by id;

  var visit_date;

run;

[c8826024]

Thanks for the response.

What about multiple variables are needed to be transposed? Say, in my original dataset, I have variables called location and dose, which will be different every patience visit.PROC TRANSPOSE seems to be only able to handle one variable transpose.

[art297]

Transpose can still do it, but it takes more than one transpose.  I like to use the method described in the following thread: http://communities.sas.com/message/48374#48374

[Linlin]

Hi Art,

How do you find the post that posted long time ago?

Thanks!

[art297]

Since I knew that I had posted that response previously, I just did a search for: tabachneck transpose.  Of course, since the forum differentiates names in its searches you may have to search for art297 transpose

[Linlin]

data have;

  informat id $3.;

  informat visit_date mmddyy10.;

  format visit_date mmddyy10.;

  input id    visit_date dose;

  cards;

001     1/1/2011 1

001      3/20/2011 2

001      4/15/2011 3

002      8/1/2011 4

002      9/2/2011 5

003      1/23/2011 6

003     2/2/2011 7

003     2/15/2011 8

003    3/8/2011 9

003    3/15/2011 10

003     3/28/2011 11

003     3/29/2011 12

003    3/31/2011 13

;

run;

proc transpose data = have out = want1 prefix = visit;

  by id;

  var visit_date;

proc transpose data = have out = want2 prefix = dose;

  by id;

  var dose;

run;

data want(drop=_name_);

  merge want1 want2;

  by id;

run;

/* order variables in the dataset */

proc sql ;

select name into : name separated by ' ' from dictionary.columns

    where libname='WORK' and memname='WANT' and lowcase(name) ne 'id'

     order by input(substr(name,anydigit(name)),best8.), substr(name,1,anydigit(name)-1) desc;

quit;

data want;

  retain id &name;

  set want;

run;

[Ksharp]

For your situation. Call execute is a good choice.

options nosource nosource2;
data have;
  informat id $3.;
  informat visit_date mmddyy10.;
  format visit_date mmddyy10.;
  input id     visit_date dose location $;
  cards;
001     1/1/2011  12 q
001      3/20/2011 2 s
001      4/15/2011 3 e
002      8/1/2011 3 e
002      9/2/2011 4 r
003      1/23/2011 4 rt
003     2/2/2011  23 t
003     2/15/2011 32 w
003    3/8/2011  43  e
003    3/15/2011 24 ew
003     3/28/2011 4 w
003     3/29/2011 4 q
003    3/31/2011 43 w
;
run;
data _null_;
 set have end=last;
 by id notsorted;
 if _n_ eq 1 then call execute('data want;');
 if first.id then do;count=0;call execute('id="'||id||'";'); end;
 count+1;
 call execute('visit_date'||strip(count)||'='||visit_date||';'||
              'dose'||strip(count)||'='||dose||';'||
              'location'||strip(count)||'="'||location||'";');
 if last.id then call execute('output;call missing(of _all_);');
 if last then call execute('format visit_date: mmddyy10.;run;');
run;

Ksharp

消息编辑者为：xia keshan

[Ksharp]

It is another way to use array which will be faster.

data have;
  informat id $3.;
  informat visit_date mmddyy10.;
  format visit_date mmddyy10.;
  input id     visit_date dose location $;
  cards;
001     1/1/2011  12 q
001      3/20/2011 2 s
001      4/15/2011 3 e
002      8/1/2011 3 e
002      9/2/2011 4 r
003      1/23/2011 4 rt
003     2/2/2011  23 t
003     2/15/2011 32 w
003    3/8/2011  43  e
003    3/15/2011 24 ew
003     3/28/2011 4 w
003     3/29/2011 4 q
;
run;
proc sql noprint;
 select max(count) into : max 
  from (select count(*) as count from have group by id);
quit;
%let max=%trim(%left(&max));
data want(keep=id visit_date_: dose_: location_:);
 set have;
 by id;
 array v{*} visit_date_1-visit_date_&max;
 array d{*} dose_1-dose_&max;
 array l{*} $ location_1-location_&max;
 retain  visit_date_: dose_: location_:;
 if first.id then count=0;
 count+1;
 v{count}=visit_date;
 d{count}=dose;
 l{count}=location;
 if last.id then do;output; call missing(of v{*} d{*} l{*});end;
 format  visit_date_: mmddyy10.;
run;

Ksharp

[MikeZdeb]

hi ... for multiple varibales ... using Ksharp's data (and PROC SQL step to find the value of &MAX), you could also try ...

proc summary data=have nway;

class id;

output out=want (drop=_type_ _freq_) idgroup(out[&max](visit_date dose location)=);

run;

ps ... from ... "Transposing Data Using PROC SUMMARY'S IDGROUP Option"

http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

[c8826024]

Thanks everybody.

I like KSHARP's solution because I am a "DATA STEP" guy. I always believe DATA STEP gives programmers more flexibilities than PROCEDURE. But sometimes PROCEDURE is easier to get your job done if the task is simple.

Thanks again.