topic Re: Transpose? in SAS Procedures

Transpose?

robertrao — Thu, 02 May 2013 15:41:22 GMT

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

Re: Transpose?

jf — Thu, 02 May 2013 16:05:28 GMT

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;

Re: Transpose?

robertrao — Thu, 02 May 2013 16:10:59 GMT

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

Re: Transpose?

jf — Thu, 02 May 2013 16:14:54 GMT

show me more scenarios.

Re: Transpose?

jf — Thu, 02 May 2013 16:34:39 GMT

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;

Re: Transpose?

art297 — Thu, 02 May 2013 16:47:11 GMT

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;

Re: Transpose?

robertrao — Thu, 02 May 2013 16:59:19 GMT

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

Re: Transpose?

art297 — Thu, 02 May 2013 17:21:39 GMT

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

Re: Transpose?

Haikuo — Thu, 02 May 2013 18:47:45 GMT

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

Re: Transpose?

Ksharp — Fri, 03 May 2013 02:48:20 GMT

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

Re: Transpose?

art297 — Fri, 03 May 2013 15:18:34 GMT

: 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

Re: Transpose?

Ksharp — Sat, 04 May 2013 02:19:22 GMT

Dr Arthur.T,

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

Ksharp