Transpose?

[robertrao]

hi i have :

ID   Date    counter  prefix
101  date1   1        out
101  date2   2        out
101  date3   3        out
101  date5   1        in
101  date6   2        in
101  date7   3        in
101  date8   4        in

i want

ID   newvar1   newvar2
101   date1     date5
101   date2     date6
101   date3     date7
101     .         date8  

Thanks

[jf]

from what you showed here, it seems NOT a transpose question. You can use ' lag3' to solve your problem.

data transpose1;

input ID   Date $    counter  prefix;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

run;

data transpose2;

set transpose1;

date5 = lag3(date);

run;

[robertrao]

But the other ID's may not have the exact scenario..they might have less or more counters!!!

Could you help in that scenario also???

Thanks

[jf]

show me more scenarios.

[jf]

data t1;

input ID   Date $    counter  prefix $;

cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

;

run;

proc sort data = t1 out = t2;

by id prefix;

run;

proc transpose data=t2 out= t3 prefix = prefix;

    by id prefix;

    id counter;

    var date;

run;

proc transpose data=t3 out= t4 prefix=prefix ;

   by id;

   var prefix1 - prefix4;

run;

[art297 · Accepted solution]

If you want to use proc transpose, I would suggest something like:

data have;

  input ID   Date $ counter  prefix $;

  cards;

101  date1   1        out

101  date2   2        out

101  date3   3        out

101  date5   1        in

101  date6   2        in

101  date7   3        in

101  date8   4        in

;

proc sort data=have;

  by id counter;

run;

proc format;

  value $prefix

    'out'=1

    'in'=2

  ;

run;

proc transpose data=have

     out=want (drop=_name_ counter)

     prefix=newvar;

  by id counter;

  var date;

  id prefix;

  format prefix $prefix.;

run;

[robertrao]

Thanks so very much. It works well.But is there any other method other than the transpose??which is the beest on eto go for in such situations??

Thanks

[art297]

Given data like your test data, I'd think that the proc transpose solution would require the least upkeep for the code.

[Haikuo]

FWIW, Here is a Hash() approach,

data have;

  input ID Date $ counter prefix $;

  cards;

101 date1 1 out

101 date2 2 out

101 date3 3 out

101 date5 1 in

101 date6 2 in

101 date7 3 in

101 date8 4 in

;

data _null_;

  if _n_=1 then do;

  if 0 then set have;

  length newvar1 newvar2 $ 8;

  declare hash h(ordered: 'y');

h.definekey('id','counter');

h.definedata('id', 'newvar1', 'newvar2');

h.definedone();

  end;

  set have end=last;

  rc=h.find();

  if prefix='out' then newvar1=date;

  else if prefix='in' then newvar2=date;

  rc=h.replace();

  if last then h.output(dataset:'want');

  run;

Haikuo

[Ksharp]

Or using self-merge skill:

data have;
  input ID   Date $ counter  prefix $;
  cards;
101  date1   1        out
101  date2   2        out
101  date3   3        out
101  date5   1        in
101  date6   2        in
101  date7   3        in
101  date8   4        in
;
run;
data want(drop=prefix);
 merge have(keep=id date prefix rename=(date=date1) where=(prefix='out')) 
       have(keep=id date prefix rename=(date=date2) where=(prefix='in'));
 by id;
 output;
 call missing(of _all_);
run;

Dr. Arthur.T , glad to see you , How is SGF2013 being ?

Ksharp

[art297]

: SGF was fantastic!  Our transpose paper was extremely well received, had almost 200 attendees, and I've been asked to present a 50 minute version this September at MWSUG as one of their featured presentations.  I didn't mention it in this thread as I didn't think it would help the OP but, if anyone is interested in knowing how to speed up transpositions up to 50 times faster than one can with proc transpose, take a look at:

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

[Ksharp]

Dr Arthur.T,

Nice. glad to see you continue to make contributions for SAS users.

Ksharp